The following bug has been logged on the website:
Bug reference: 7888
Logged by: Jan Krajdl
Email address: spm@spamik.cz
PostgreSQL version: Unsupported/Unknown
Operating system: all
Description: =
Hi,
I have found small issue on this wiki page:
http://wiki.postgresql.org/wiki/Round_time
There is mistake in "alternative approach" function - select
'1970-01-01'::timestamptz results in timestamp '1970-01-01 00:00:00' with
local timezone. But function is using it as a start of epoch which was same
time but in timezone GMT. If you use this function and you have postgres
server in non GMT timezone, rounded results are in GMT timezone but they
have timezone information about local timezone.
I'm quite noob with postgres but I tried to fix it and it looks that this
code:
CREATE FUNCTION date_round(base_date timestamptz, round_interval INTERVAL)
RETURNS timestamptz AS $BODY$
SELECT TIMESTAMP WITH TIME ZONE 'epoch' + (EXTRACT(epoch FROM $1)::INTEGER +
EXTRACT(epoch FROM $2)::INTEGER / 2)
/ EXTRACT(epoch FROM $2)::INTEGER * EXTRACT(epoch FROM
$2)::INTEGER * INTERVAL '1 second';
$BODY$ LANGUAGE SQL STABLE;
is doing it better - returned time looks correct. So if you want you can
update this page.
Regards,
-- =
Jan Krajdl