CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerytimestamp
dataroot
dataroot

Reputation: 45

Timestamp of yesterday at specific hour bigquery

I need to schedule a query on BigQuery, that will retrieve some data from 24h ago. The problem is that for example if its scheduled for 5am, the query needs time to be executed (let's say 2.248 seconds), and so the yesterday's data from 5:00:00.000 to 5:00:02.248 will not be retrieved. I'm using a timestamp field, and i do something like this :

SELECT *
FROM my_table
WHERE my_timestamp >= TIMESTAMP_SUB(current_timestamp(), INTERVAL 24 hour)

I would like to know if there is a way to get the yesterday's date, at a specific hour, so even if there is a little gap due to the execution, it will still retrieve data from a specific hour.

edit: I found something :

SELECT *
FROM my_table
WHERE my_timestamp >=  TIMESTAMP(DATE_SUB(current_date(), INTERVAL 1 DAY) )
AND my_timestamp < TIMESTAMP(current_date)

But this retrieves yesterday between 00:00:00.000 to 23:59:59.999

It is okay but is there a way to choose the hour ?

Upvotes: 1

Views: 900

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Consider below least verbose approach

select *
from my_table
where my_timestamp >=  timestamp(current_date - 1 + interval 5 hour)
and my_timestamp < timestamp(current_date + interval 5 hour)

Upvotes: 1

dataroot
dataroot

Reputation: 45

Okay I found on my own lol, maybe it will help someone else looking for this.

SELECT *
FROM my_table
WHERE my_timestamp >=  TIMESTAMP_ADD(TIMESTAMP(DATE_SUB(current_date, INTERVAL 1 DAY)), INTERVAL 5 HOUR)
AND my_timestamp < TIMESTAMP_ADD(TIMESTAMP(current_date), INTERVAL 5 HOUR)

Upvotes: 1

Related Questions