BUG #7888: Small issue on wiki page Round time

It is a wiki, so you can easily get an account and make the change
yourself. Thanks.

---------------------------------------------------------------------------

On Sun, Feb 17, 2013 at 07:14:00PM +0000, spm@spamik.cz wrote:

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs