Have you ever encountered a situation where you would need to round a timestamp to the nearest second, 5 seconds or minute? While rounding a number is trivial, it's a little bit less straightforward when dealing with timestamps.
Let's consider the following input data.
We have our event_times that are at milliseconds-level grain. To round the times to the nearest granularity we'd like, we can use a combination of UNIX_MILLIS and TIMESTAMP_MILLIS.
The steps are as follows:
Transform the timestamp into milliseconds since Unix epoch (that is, number of milliseconds that have passed since January 1st, 1970) with UNIX_MILLIS
Divide that number by your target grain in milliseconds i.e. 1000 for 1 second or 60000 for 1 minute
CAST that number to an INT64
Multiply that number by your target grain (the number from above) i.e. 1000 for 1 second or 60000 for 1 minute
Transform it back to a timestamp using TIMESTAMP_MILLIS
SELECT
event_time,
UNIX_MILLIS(event_time) AS epoch_milliseconds,
TIMESTAMP_MILLIS( CAST(UNIX_MILLIS(event_time) / 1000 AS INT64) * 1000) AS nearest_second,
TIMESTAMP_MILLIS( CAST(UNIX_MILLIS(event_time) / 5000 AS INT64) * 5000) AS nearest_5seconds,
TIMESTAMP_MILLIS( CAST(UNIX_MILLIS(event_time) / 60000 AS INT64) * 60000) AS nearest_1minute,
TIMESTAMP_MILLIS( CAST(UNIX_MILLIS(event_time) / 300000 AS INT64) * 300000) AS nearest_5minutes,
FROM input_data
Upon executing this query, the following results are produced:
The functions presented above and others relevant to working with timestamps are presented in the documentation.
Thanks for reading and enjoy working with BigQuery!