sqlgoogle-bigqueryno-data

Results : There is no data to display

 SELECT
   *
 FROM 
`fleet-gamma-355414.bikes_data.12_months_data` 
 WHERE
  started_at BETWEEN '2020-06-01'AND '2020-06-01'
 ORDER BY
  started_at;

So I was to get the dates that only show from one month (June) and sorted in ascending order, but running the query gave this result.

There is no data to display.

Here is the SCHEMA

:Field name: Type: Mode: Collation: Default: Value: Policy: Tags: Description:
------------------------------------------------------------------------

 ride_id             STRING     NULLABLE            
 rideable_type       STRING     NULLABLE            
 started_at          TIMESTAMP  NULLABLE            
 ended_at            TIMESTAMP  NULLABLE            
 start_station_name  STRING     NULLABLE            
end_station_name     STRING     NULLABLE            
start_lat            FLOAT      NULLABLE            
start_lng            FLOAT      NULLABLE            
end_lat              FLOAT      NULLABLE            
end_lng              FLOAT      NULLABLE            
member_casual        STRING     NULLABLE

Sorry, I don't know why the table or the query is like that. I don't know how to edit it started_at is TIMESTAMP

Solution

  • [Why] There is no data to display.

    Because started_at is of timestamp data type

    started_at BETWEEN '2020-06-01'AND '2020-06-01'

    is equivalent to

    started_at BETWEEN '2020-06-01 00:00:00'AND '2020-06-01 00:00:00'

    and thus looks like there is no data for that exact second present in your table

    I don't know how to edit it

    To resolve this - you should cast started_at into date data type - which is done with help of DATE() function - so now you really looking for data anywhere within 2020-06-01 date

    So, use below

     SELECT
   *
 FROM 
`fleet-gamma-355414.bikes_data.12_months_data` 
 WHERE
  DATE(started_at) BETWEEN '2020-06-01'AND '2020-06-01'
 ORDER BY
  started_at;