85
Postgres can round (truncate) timestamps using the date_trunc function, like this:
date_trunc('hour', val)
date_trunc('minute', val)
I'm looking for a way to truncate a timestamp to the nearest 5-minute boundary so, for example, 14:26:57 becomes 14:25:00. The straightforward way to do it is like this:
date_trunc('hour', val) + date_part('minute', val)::int / 5 * interval '5 min'
Since this is a performance-critical part of the query, I'm wondering whether this is the fastest solution, or whether there's some shortcut (compatible with Postgres 8.1+) that I've overlooked.