PopSQL

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
From BigQuery query to chart to Slack in seconds
Get to answers faster, together, with PopSQL and BigQuery