- Explore
- Collaborate
- Visualize
- Connect
- Pricing
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 | ✅ | ✅ | ✅ |