Round time

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 to_timestamp((EXTRACT(epoch FROM $1)::integer + EXTRACT(epoch FROM $2)::integer / 2)
                / EXTRACT(epoch FROM $2)::integer * EXTRACT(epoch FROM $2)::integer)
$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');