datetime query of particular hours for entire year-CodePudding

<table><tbody><tr><th>Row</th><th>Identifier</th><th>date_time</th><th>HvacMode</th><th>CalendarEvent</th></tr><tr><td>1</td><td>a578bb21941003e7a58a399375b20f274fab5233</td><td>2020-10-31 14:50:00 UTC</td><td>heat</td><td> </td></tr><tr><td>2</td><td>683227685eb44f9d231d511e8ae5255ecc1b71fb</td><td>2019-08-19 02:00:00 UTC</td><td>cool</td><td> </td></tr><tr><td>3</td><td>1afd0d7a8408458d39dde687e1218f2948907c4b</td><td>2021-07-19 19:40:00 UTC</td><td>auto</td><td> </td></tr><tr><td>4</td><td>607db2c120782353da1539a008956301341d54c9</td><td>2020-08-29 16:50:00 UTC</td><td>cool</td><td> </td></tr><tr><td> </td><td> </td><td> </td><td> </td><td> </td></tr></tbody></table>

I want to get the data for a particular time period 7am-7pm (7:00-19:00) for each day of the year. I have a datetime field in the format 2017:01:01 00:00:00 (example). A bit of a newbie here. I know how to extract for a specific date range, but I don't know how to extract for a date range of particular hours. Working with a very large dataset in BigQuery. Any assistance would be helpful. sample data image

CodePudding user response：

If I am understanding your question you are trying to filter each day of data by the 7-7 time period. Try the example below:

with sample_data as (
    SELECT ts FROM UNNEST(generate_timestamp_array('2021-01-01 00:00:00', '2021-03-01 00:00:00', INTERVAL 1 hour)) as ts
)

select *
from sample_data
where 
extract(year from ts) = 2021
and extract(hour from ts) between 7 and 19;

Using the extract function, you can pull the hour from the timestamp and then filter on a between. For more information on the extract function refer to the following documentation: https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#extract

CodePudding user response：

All;

This worked for the data I need. Thank you for your help. The snippet below gives me the daytime hours for the month of January.

select *
from sample_data where extract(hour from date_time) between 7 and 19
AND date_time between '2017-01-01 00:00:00' and '2017-01-31 23:59:00';