The following bug has been logged on the website: Bug reference: 7888 Logged by: Jan Krajdl Email address: s...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
