How to Query Date and Time in BigQuery
BigQuery has four date and time data types. Each data type its own associated functions and accepted parameters.
Get the date and/or time right now:
select current_timestamp; -- date and time, with timezone select current_datetime; -- date and time, without timezone select current_date; -- date select current_time; -- time
Find rows between two absolute timestamps:
select * from table where created_at >= timestamp1 and created_at < timestamp2
The syntax above works for
Find rows created within the last week:
select * from table where timestamp > timestamp_sub(current_timestamp, INTERVAL 7 DAY);
- Only integers are accepted, i.e. you can't write
INTERVAL 1.5 DAY.
- Keep the time unit singular. It's
INTERVAL 7 DAY, not
INTERVAL 7 DAYS.
DAYis largest unit accepted for
time_subwhen working with other data types.
Find rows created between one and two weeks ago:
select * from table where timestamp > timestamp_sub(current_timestamp, INTERVAL 14 DAY) and timestamp_sub(current_timestamp, INTERVAL 7 DAY);
Extracting part of a timestamp:
select extract(minute from timestamp); --or datetime, date, time
Get the day of the week from a timestamp:
--returns 1-7, where 1 is Sunday and 7 is Saturday. select extract(dayofweek from timestamp); -- returns a string like Monday, Tuesday, etc select format_timestamp('%A',timestamp); --
Converting a timestamp to a unix timestamp (integer seconds):
select unix_seconds(current_timestamp); select unix_seconds(timestamp('2020-05-09 14:53:21'));
Calculate the difference between two timestamps:
-- timestamp select timestamp_diff(timestamp2, timestamp1, SECOND); -- datetime select datetime_diff(datetime2, datetime1, SECOND); -- date select date_diff(date2, date1, DAY); -- time select time_diff(time2, time1, SECOND);
Here's a handy table of the time units accepted as arguments in BigQuery date and time functions (e.g.
Spread the wordTweet