Re: rounding a timestamp to nearest x seconds

On 03/07/2012 08:11 PM, Daniele Varrazzo wrote:
> 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
>

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

-Andy