On 03/07/2012 08:11 PM, Daniele Varrazzo wrote:
On Wed, Mar 7, 2012 at 3:09 PM, Andy Colson<andy@xxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
