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
-- Round to the nearest minute SELECT DATE_TRUNC('minute', '2021-06-01 12:31:56' + INTERVAL '30 second')
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')