Round time

From PostgreSQL wiki

Revision as of 10:53, 10 September 2012 by Myurr (Talk | contribs)

Jump to: navigation, search

Library Snippets

Rounds a timestamp to the nearest 5 minute mark

Works with PostgreSQL

8.2

Written in

PL/pgSQL

Depends on

Nothing


Round a timestamp to the nearest 5 minute mark.

CREATE OR REPLACE FUNCTION round_time(TIMESTAMP WITH TIME ZONE) 
RETURNS TIMESTAMP WITH TIME ZONE AS $$ 
  SELECT date_trunc('hour', $1) + INTERVAL '5 min' * ROUND(date_part('minute', $1) / 5.0) 
$$ LANGUAGE SQL;

sample usage:

postgres=# select now(), round_time('2010-09-17 16:48');
              now              |       round_time       
-------------------------------+------------------------
 2010-09-19 08:36:31.701919+02 | 2010-09-17 16:50:00+02
(1 row)

postgres=# select now(), round_time('2010-09-17 16:58');
              now              |       round_time       
-------------------------------+------------------------
 2010-09-19 08:36:43.860858+02 | 2010-09-17 17:00:00+02
(1 row)

postgres=# select now(), round_time('2010-09-17 16:57');
              now              |       round_time       
-------------------------------+------------------------
 2010-09-19 08:36:53.273612+02 | 2010-09-17 16:55:00+02
(1 row)

postgres=# select now(), round_time('2010-09-17 23:58');
             now              |       round_time       
------------------------------+------------------------
 2010-09-19 08:37:09.41387+02 | 2010-09-18 00:00:00+02
(1 row)


Alternative approach:

CREATE FUNCTION date_round(base_date timestamptz, round_interval INTERVAL) RETURNS timestamptz AS $BODY$
SELECT '1970-01-01'::timestamptz + (EXTRACT(epoch FROM $1)::INTEGER + EXTRACT(epoch FROM $2)::INTEGER / 2)
                / EXTRACT(epoch FROM $2)::INTEGER * EXTRACT(epoch FROM $2)::INTEGER * INTERVAL '1 second';
$BODY$ LANGUAGE SQL STABLE;

This will also work across one hour boundaries, e.g. round to nearest hour and a half. Sample usage:

SELECT date_round(now(), '15 minutes');
Personal tools