Thread: BUG #6605: wrong type cast from timestamp to timestamptz
The following bug has been logged on the website:
Bug reference: 6605
Logged by: Sergey Burladyan
Email address: eshkinkot@gmail.com
PostgreSQL version: 9.1.3
Operating system: Debian testing
Description:=20=20=20=20=20=20=20=20
Postgres from Debian package:
PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian
4.6.3-1) 4.6.3, 64-bit
Good:
set timezone to 'Europe/Moscow'; select '2011-03-27
23:00:00'::timestamptz;
SET
=D0=92=D1=80=D0=B5=D0=BC=D1=8F: 0,092 =D0=BC=D1=81
timestamptz=20=20=20=20=20=20=20
------------------------
2011-03-27 23:00:00+04
Bad:
set timezone to 'W-SU'; select '2011-03-27 23:00:00'::timestamptz;
SET
=D0=92=D1=80=D0=B5=D0=BC=D1=8F: 0,106 =D0=BC=D1=81
timestamptz=20=20=20=20=20=20=20
------------------------
2011-03-28 02:59:54+04
Good again:
set timezone to 'W-SU'; select '2011-03-27 23:00:01'::timestamptz;
SET
=D0=92=D1=80=D0=B5=D0=BC=D1=8F: 0,135 =D0=BC=D1=81
timestamptz=20=20=20=20=20=20=20
------------------------
2011-03-27 23:00:01+04
For information, 2011-03-27 the last day when Daylight Saving Time was used.
After it Daylight Saving Time is canceled in Russia:
W-SU is symlink:
$ ls -la /usr/share/zoneinfo/Europe/Moscow=20
lrwxrwxrwx 1 root root 7 =D0=9C=D0=B0=D1=80 6 22:39 /usr/share/zoneinfo/Eu=
rope/Moscow ->
../W-SU
$ zdump -v W-SU | tail
W-SU Sat Oct 24 22:59:59 2009 UTC =3D Sun Oct 25 02:59:59 2009 MSD isdst=
=3D1
gmtoff=3D14400
W-SU Sat Oct 24 23:00:00 2009 UTC =3D Sun Oct 25 02:00:00 2009 MSK isdst=
=3D0
gmtoff=3D10800
W-SU Sat Mar 27 22:59:59 2010 UTC =3D Sun Mar 28 01:59:59 2010 MSK isdst=
=3D0
gmtoff=3D10800
W-SU Sat Mar 27 23:00:00 2010 UTC =3D Sun Mar 28 03:00:00 2010 MSD isdst=
=3D1
gmtoff=3D14400
W-SU Sat Oct 30 22:59:59 2010 UTC =3D Sun Oct 31 02:59:59 2010 MSD isdst=
=3D1
gmtoff=3D14400
W-SU Sat Oct 30 23:00:00 2010 UTC =3D Sun Oct 31 02:00:00 2010 MSK isdst=
=3D0
gmtoff=3D10800
W-SU Sat Mar 26 22:59:59 2011 UTC =3D Sun Mar 27 01:59:59 2011 MSK isdst=
=3D0
gmtoff=3D10800
W-SU Sat Mar 26 23:00:00 2011 UTC =3D Sun Mar 27 03:00:00 2011 MSK isdst=
=3D0
gmtoff=3D14400
W-SU 9223372036854689407 =3D NULL
W-SU 9223372036854775807 =3D NULL
I also see this problem in my other server with 9.0:
select '2011-03-27 23:00:00'::timestamptz;
timestamptz=20=20=20=20=20=20=20
------------------------
2068-04-02 03:00:00+04
(1 row)
eshkinkot@gmail.com writes:
> set timezone to 'W-SU'; select '2011-03-27 23:00:00'::timestamptz;
> SET
> timestamptz
> ------------------------
> 2011-03-28 02:59:54+04
Bizarre. On my Fedora 16 box, I see a different misbehavior:
regression=# set timezone to 'Europe/Moscow'; select '2011-03-27 23:00:00'::timestamptz;
SET
timestamptz
------------------------
2011-03-28 00:29:40+04
(1 row)
(W-SU behaves the same, incidentally.) Two other machines are fine
with this case, though. It may or may not be relevant that the F16
build is using --with-system-tzdata, as I imagine your Debian package
is also, while the machines that are happy are not.
So: some platform-specific misbehavior here. I have no time to poke at
it more now, though. Who else can reproduce this, on what platforms?
regards, tom lane
Saturday, April 21, 2012, 7:49:55 PM you wrote: > So: some platform-specific misbehavior here. I have no time to poke at > it more now, though. Who else can reproduce this, on what platforms? One machine works, the other not: PostgreSQL 9.1.3 on i686-pc-linux-gnu, compiled by i686-pc-linux-gnu-gcc (Gentoo 4.5.3-r2 p1.0, pie-0.4.6) 4.5.3, 32-bit Result: 2011-03-27 23:00:00+04 PostgreSQL 9.1.3 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (Gentoo 4.5.3-r2 p1.0, pie-0.4.6) 4.5.3, 64-bit Result: 2011-03-28 00:29:40+04 -- Jochen Erwied | home: jochen@erwied.eu +49-208-38800-18, FAX: -19 Sauerbruchstr. 17 | work: joe@mbs-software.de +49-2151-7294-24, FAX: -50 D-45470 Muelheim | mobile: jochen.erwied@vodafone.de +49-173-5404164
Jochen Erwied <jochen@pgsql-bugs.erwied.eu> writes:
> Saturday, April 21, 2012, 7:49:55 PM you wrote:
>> So: some platform-specific misbehavior here. I have no time to poke at
>> it more now, though. Who else can reproduce this, on what platforms?
> One machine works, the other not:
I traced though it far enough to find that pg_next_dst_boundary indexes
off the end of an array when the given probe time is exactly the last
DST transition time for the zone. So what's surprising is not that
it fails weirdly, but that there seem to be numerous machines where
it doesn't (appear to) fail. The next array slot must chance to have
a sane value in some environments.
regards, tom lane
On 22/04/12 05:49, Tom Lane wrote:
> eshkinkot@gmail.com writes:
>> set timezone to 'W-SU'; select '2011-03-27 23:00:00'::timestamptz;
>> SET
>> timestamptz
>> ------------------------
>> 2011-03-28 02:59:54+04
> Bizarre. On my Fedora 16 box, I see a different misbehavior:
>
> regression=# set timezone to 'Europe/Moscow'; select '2011-03-27 23:00:00'::timestamptz;
> SET
> timestamptz
> ------------------------
> 2011-03-28 00:29:40+04
> (1 row)
>
> (W-SU behaves the same, incidentally.) Two other machines are fine
> with this case, though. It may or may not be relevant that the F16
> build is using --with-system-tzdata, as I imagine your Debian package
> is also, while the machines that are happy are not.
>
> So: some platform-specific misbehavior here. I have no time to poke at
> it more now, though. Who else can reproduce this, on what platforms?
>
> regards, tom lane
>
I get strange results on Fedora 16 (I think I upgraded straight from 14)!
$ psql
psql (9.1.3)
Type "help" for help.
gavin=> set timezone to 'Europe/Moscow'; select '2011-03-27
23:00:00'::timestamptz;
SET
timestamptz
------------------------
2010-05-02 05:46:24+04
(1 row)
gavin=> \q
$ uname -a
Linux saturn 3.3.2-1.fc16.x86_64 #1 SMP Sat Apr 14 00:31:23 UTC 2012
x86_64 x86_64 x86_64 GNU/Linux
$ date
Sun Apr 22 09:27:10 NZST 2012
$
eshkinkot@gmail.com writes:
> set timezone to 'W-SU'; select '2011-03-27 23:00:00'::timestamptz;
> SET
> timestamptz
> ------------------------
> 2011-03-28 02:59:54+04
I've applied a patch for this. Thanks for the report!
regards, tom lane