Round Timestamps

To round timestamps in SQL, you can use the DATE_TRUNC function with a rounding unit parameter.

Consider the following “sales” table:

To round a timestamp to the nearest hour, you can use:

SELECT DATE_TRUNC('hour', transaction_date) as rounded_timestamp
FROM sales;

In this example, “transaction_date” is the name of the column containing the timestamp values, and table_name is the name of the table containing the data.

The DATE_TRUNC function accepts various rounding unit parameters, including ‘year’, ‘quarter’, ‘month’, ‘week’, ‘day’, ‘hour’, ‘minute’, ‘second’, and others. You can use the appropriate rounding unit parameter to round the timestamps to the desired precision.

Here’s the result:

Note that the exact syntax and supported date/time functions may vary depending on the specific SQL dialect used in your database.

This is often useful when performing GROUP BY aggregation of discrete events (with second or sub-second resolution) at various time domain granularity: hourly events, daily sales, monthly renewals, etc.