Round timestamp to the nearest minute, hour… in PostgreSQL

Round timestamp to the nearest minute, hour… in PostgreSQL

There is no ROUND function for timestamps in PostgreSQL, that would round to the nearest time unit. You can use DATE_TRUNC() to truncate down the time, but it does never round up.

A trick is to add half of the time interval, and then apply DATE_TRUNC().

-- Round to the nearest minute
SELECT DATE_TRUNC('minute', '2021-06-01 12:31:56' + INTERVAL '30 second')

2021-06-01 12:31:00

It works for any PostgreSQL time interval:

-- Round to the nearest hour
SELECT DATE_TRUNC('hour', '2021-06-01 12:31:56' + INTERVAL '30 minute')

2021-06-01 13:00:00