Round time

From PostgreSQL wiki

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)