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