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