Efficient time rounding function

Question

I am writing a custom postgresql function to round TIMESTAMPTZ fields to an arbitrary interval with the basic algorithm of round(timestamp / interval) * interval and some research, I found a solution:

SELECT to_timestamp(round((extract('epoch' from timestamp)) / interval) * interval)

it works. My question: is there a more efficient way of doing this?

Answer 1

Look this like a template: Round a timestamp to the nearest 5-minute mark.

The 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)