Coginiti menu Coginiti menu

Round Timestamps

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

Consider the following “sales” table:
Table with two columns. The first one is “transaction_date,” containing timestamps. The second is “amount,” with integer numbers representing the number of sales.

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:
Table with the “rounded_timestamp” 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.