Re: rounding a timestamp to nearest x seconds

On Wed, Mar 7, 2012 at 3:09 PM, Andy Colson <andy(at)squeakycode(dot)net> wrote:
> 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..

Your function can only round the seconds: it cannot round on intervals
longer than one minute and always rounds down to the minute, creating
irregular intervals, e.g.:

=> select round_timestamp('2012-03-12 01:42:58', 13);
2012-03-12 01:42:52
=> select round_timestamp('2012-03-12 01:42:59', 13);
2012-03-12 01:43:05
=> select round_timestamp('2012-03-12 01:43:00', 13);
2012-03-12 01:43:00

You don't get discontinuities if you map the timestamp on the real
axis by extracting the epoch, play there and then go back into the
time domain:

create or replace function round_timestamp(timestamp, integer) returns
timestamp as $$
select 'epoch'::timestamp + '1 second'::interval * ($2 *
round(date_part('epoch', $1) / $2));
$$ language sql immutable;

This version can round on any interval specified in seconds (but it
would be easy to specify the step as interval: date_part('epoch',
interval) returns the interval length in seconds).

-- Daniele