- Explore
- Collaborate
- Visualize
- Connect
- Pricing
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
, notINTERVAL 7 DAYS
. - Annoyingly,
DAY
is largest unit accepted fortimestamp_sub()
- Use
datetime_sub()
,date_sub()
, ortime_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 | ✅ | ✅ |
Finally, a unified workspace for your SQL development
Get more done, together, with PopSQL and BigQuery
Previous
Finally, a unified workspace for your SQL development
Get more done, together, with PopSQL and BigQuery