Thread: rounding a timestamp to nearest x seconds

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

On Wed, Mar 7, 2012 at 3:09 PM, Andy Colson <andy@squeakycode.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

On 03/07/2012 08:11 PM, Daniele Varrazzo wrote:
> On Wed, Mar 7, 2012 at 3:09 PM, Andy Colson<andy@squeakycode.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
>

Oh, that's very nice, thank you.  Never even thought of using epoch.

-Andy