I’m working on a time billing application and needed a way to round punches to the nearest 15 minute increment from within the Postgres DB. The function below is the most elegant solution I could come up with. It could be easily modified to always round-up or round-down by replacing ROUND() with either CEIL() or FLOOR(), respectively.
CREATE OR REPLACE FUNCTION round_timestamp(
ts timestamptz
,round_secs int
) RETURNS timestamptz AS $$
DECLARE
_mystamp timestamp;
_round_secs decimal;
BEGIN
_round_secs := round_secs::decimal;
_mystamp := timestamptz 'epoch'
+ ROUND((EXTRACT(EPOCH FROM ts))::int / _round_secs) * _round_secs
* INTERVAL '1 second';
RETURN _mystamp;
END; $$ LANGUAGE plpgsql IMMUTABLE;
SELECT * FROM round_timestamp('2010-03-04 11:39:11', 900);
round_timestamp
------------------------
2010-03-04 11:45:00-05
(1 row)