How to Round Timestamps in BigQuery

Rounding/truncating timestamps, datetimes, etc is helpful when you're grouping by time. In BigQuery, the function you need varies depending on the data type of your schema:

Timestamp

select timestamp_trunc(current_timestamp, HOUR);

Datetime

select datetime_trunc(current_datetime, HOUR);

Date

select date_trunc(current_date, DAY);

Time

select time_trunc(current_time, HOUR);

Depending on the data type, different time units are accepted for each truncating function:

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

Cta

From BigQuery query to chart to Slack in seconds

Get to answers faster, together, with PopSQL and BigQuery