Sign In

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 ✅ ✅
Cta
Finally, a unified workspace for your SQL development
Get more done, together, with PopSQL and BigQuery
Get started in minutes
Previous
Date and Time Data Types
Next
How to Group by Time