Talk:Round time
From PostgreSQL wiki
The "alternative approach" is unfortunately broken. For example:
=# set timezone to 'Europe/Paris'; SET =# select date_round('0001-01-01', '1 hour'); ERROR: integer out of range =# \ef date_round -- OK, fine, use bigint instead of integer CREATE FUNCTION =# select date_round('0001-01-01', '1 hour'); date_round ------------------------------ 0001-01-01 01:09:21+00:09:21 (1 row)
Even today:
=# set timezone to 'Canada/Newfoundland'; SET =# select now(), date_round(now(), '1 hour'); now | date_round ----------------------------------+--------------------------- 2014-12-16 20:34:26.493851-03:30 | 2014-12-16 20:30:00-03:30 (1 row)
Unfortunately I don't see an easy way to fix it if while keeping the functionality for doing completely arbitrary intervals, e.g. the "an hour and a half" stated in the example. Anyone else see a reasonable way? Otherwise I'd be inclined to just rip it out, or replace with a version which supports only certain intervals, not arbitrary ones.