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:

 TimestampDatetimeDateTime
microsecond 
millisecond 
second 
minute 
hour 
day 
week 
month 
quarter 
year 
database icon
Better SQL for the people
Get more done with PopSQL and BigQuery