CODEWITHSUNDEEP
sqlgoogle-bigquery
ebuzz168
ebuzz168

Reputation: 1184

Extract hours between two date on bigquery standard sql

I'm trying to extract data from between date and time range using StandardSQL in Bigquery

Basically what I do is, take the data from between defined date to the current date and from 9 am at a defined date to 9 am current date.

I tried to use this query but it only returns the data from the 9 am defined date to only 11;59 pm that defined date, I can't find the data from the current date. But if I removed the constraint EXTRACT(HOUR FROM TIMESTAMP(DATETIME(registration_started_at, 'Asia/Jakarta'))) >= 9 it can returns the date range I want but not the time range

SELECT * 
from `table`
WHERE (DATE(DATETIME(registration_started_at, 'Asia/Jakarta')) BETWEEN '2021-04-30' AND DATE(DATETIME(CURRENT_TIMESTAMP(), 'Asia/Jakarta')))
ORDER BY registration_updated_at ASC) a
WHERE EXTRACT(HOUR FROM DATETIME(CURRENT_TIMESTAMP(), 'Asia/Jakarta')) < 9
AND EXTRACT(HOUR FROM TIMESTAMP(DATETIME(registration_started_at, 'Asia/Jakarta'))) >= 9

Sample Data
+---------+------------------------------+
|   ID    |   registration_started_at    |
+---------+------------------------------+
|    1    |  2021-04-29 07:44:08.864 UTC |    
|    2    |  2021-04-30 11:46:58.451 UTC |
|    3    |  2021-04-30 20:10:27.748 UTC |   
|    4    |  2021-04-30 20:39:47.206 UTC |  
+---------+------------------------------+

Expected Output (local timezone)--not the real output of the time column above

+---------+------------------------------+
|   ID    |   registration_started_at    |
+---------+------------------------------+
|    2    |  2021-04-30T09:04:52.001000  |    
|    3    |  2021-04-30T11:02:43.232000  |
|    4    |  2021-05-01T08:00:10.569000  |    
+---------+------------------------------+

Upvotes: 0

Views: 1581

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Your code is rather confusing. DATETIME does not have a time zone, so I don't really see why you need to extract the date using a time zone.

I think you just want a range from '2021-04-30 09:00:00' through (but not including '2021-05-01 09:00:00').

If so, subtract 9 hours from the DATETIME value and compare to the date:

DATE(DATETIME_ADD(registration_started_at, INTERVAL -9 HOUR)) = DATE('2021-04-30')

Upvotes: 1

Related Questions