How to Round Timestamps in BigQuery

Rounding timestamps is a common task in BigQuery when you need to group data by hour, day, or minute, or simplify time-based queries. BigQuery offers powerful SQL functions to help with this.

Using TIMESTAMP_TRUNC

The TIMESTAMP_TRUNC function rounds a timestamp down to the nearest specified part, such as hour, day, or minute.

SELECT TIMESTAMP_TRUNC(TIMESTAMP '2025-05-07 14:23:45', HOUR) AS rounded_hour;
-- Result: 2025-05-07 14:00:00 UTC

You can also round to the day or minute:

SELECT TIMESTAMP_TRUNC(TIMESTAMP '2025-05-07 14:23:45', DAY) AS rounded_day;
-- Result: 2025-05-07 00:00:00 UTC

SELECT TIMESTAMP_TRUNC(TIMESTAMP '2025-05-07 14:23:45', MINUTE) AS rounded_minute;
-- Result: 2025-05-07 14:23:00 UTC

Rounding to the Nearest Interval

If you need to round to the nearest interval (for example, nearest 15 minutes), you can use arithmetic on UNIX timestamps:

SELECT TIMESTAMP_SECONDS(
    ROUND(UNIX_SECONDS(TIMESTAMP '2025-05-07 14:23:45') / (15 * 60)) * (15 * 60)
) AS rounded_15_min;
-- Result: 2025-05-07 14:15:00 UTC

Formatting Rounded Timestamps

To display the rounded timestamps nicely, use FORMAT_TIMESTAMP:

SELECT FORMAT_TIMESTAMP('%Y-%m-%d %H:%M', TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), HOUR)) AS formatted_hour;

Summary

• TIMESTAMP_TRUNC → rounds down to unit (hour, day, minute)
• ROUND(UNIX_SECONDS(...)) → rounds to nearest interval (e.g., 15 min)
• FORMAT_TIMESTAMP → formats the result for display

With these techniques, you can confidently handle time-based rounding in BigQuery and improve the accuracy and readability of your queries.