rounding a timestamp to nearest x seconds - Mailing list pgsql-general

Took me a while to figure this out, thought I'd paste it here for others
to use:

create or replace function round_timestamp(timestamp, integer) returns
timestamp as $$
    select date_trunc('minute', $1) + cast(round(date_part('seconds',
$1)/$2)*$2 || ' seconds' as interval);
$$ language sql immutable;


If you pass 10 to the second argument, it'll round the timestamp to the
nearest 10 seconds.  Pass 5 to round to nearest 5 seconds, etc..

-Andy