Re: Bug#311533: Invalid timestamp returned because of timezone

Hi Guillaume, hi PostgreSQL developers!

Guillaume, thanks for the great research you did!

PostgreSQL developers, the details of this are at [1], but I quote the
important bits here.

Guillaume Beaudoin [2005-06-01 11:57 -0400]:
>
> Package: postgresql
> Version: 7.4.7-6sarge1
>
> When changing the time zone of a session, postgresql start to respond
> with erronous date such as 1979-06-06 09:42:49.854158-227814:06.
>
>The following :
>
>SELECT NOW();
>SET TIME ZONE INTERVAL '-05:00' HOUR TO MINUTE;
>SELECT NOW();
>
>output those two dates :
>
>2005-06-02 09:05:58.991119-04
>1979-06-07 06:59:03.246652-227814:06
>
> Is there a hope that a fix will be included in the upcoming sarge
> release? What can I do to fix the problem if not?

No, at that time Sarge was in icecold freeze. :-)

> As per conversation in #postgresql in freenode, it has been found that
> this seems to manifest on pgsql compiled using integer-datetime;
> float-datetime version does not have this problem.

I just tried to do the proposed change, however, it is not possible to
start the new postmaster on an already existing cluster. You had to
dump all clusters with the old postmaster, install the new one and
recreate the clusters, which is a hell of an upgrade (so it's
definitively nothing for Sarge, even less for sarge-proposed updates).
So I can't apply that change for now.

The cleanest one would obviously be to fix integer timestamps, or if
that is not possible, at least support selecting integer or float time
stamps at runtime (maybe as a postmaster option). Can this be done in
any way?

Thanks in advance for any thought and have a nice day!

Martin

[1] http://bugs.debian.org/311533

--
Martin Pitt http://www.piware.de
Ubuntu Developer http://www.ubuntu.com
Debian Developer http://www.debian.org