Re: 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(at)spamik(dot)cz wrote:
> The following bug has been logged on the website:
>
> Bug reference: 7888
> Logged by: Jan Krajdl
> Email address: spm(at)spamik(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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