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 datetime, date, and time.

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.
  • Annoyingly, DAY is largest unit accepted for timestamp_sub()
  • Use datetime_sub(), date_sub(), or time_sub when 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. extract, timestamp_sub, or date_diff):

Timestamp Datetime Date Time
microsecond
millisecond
second
minute
hour
day
week
month
quarter
year