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
SQL editing that just rocks
PopSQL and BigQuery, better together