0
 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

nbk
  • 45,398
  • 8
  • 30
  • 47
Tails0298
  • 27
  • 7
  • have you checked if there is data for that day? – nbk Aug 13 '22 at 12:21
  • 1
    That means there is not result witihin that date correct? Because I thought I put 2020-06-01 AND 2020-06-30, I instead put 2020-06-01 and 2020-06-01. Now I see data from that specific date. – Tails0298 Aug 13 '22 at 12:26
  • Hey how you edit the query to make look like a query and the table, because I don't see that works for me – Tails0298 Aug 13 '22 at 12:27
  • i would add hours to the query So tat you have `BETWEEN '2020-06-01 00:00:00' AND '2020-06-02 00:00:00'` – nbk Aug 13 '22 at 12:43
  • 1
    As **TIMESTAMP**, '2020-06-01' is same as '2020-06-01 00:00:00', I think that's why you dont have any result by the between condition. – Jaytiger Aug 13 '22 at 12:50

1 Answers1

1

[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;
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230