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:
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
Время: 0,092 мс
timestamptz
------------------------
2011-03-27 23:00:00+04
Bad:
set timezone to 'W-SU'; select '2011-03-27 23:00:00'::timestamptz;
SET
Время: 0,106 мс
timestamptz
------------------------
2011-03-28 02:59:54+04
Good again:
set timezone to 'W-SU'; select '2011-03-27 23:00:01'::timestamptz;
SET
Время: 0,135 мс
timestamptz
------------------------
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
lrwxrwxrwx 1 root root 7 Мар 6 22:39 /usr/share/zoneinfo/Europe/Moscow ->
../W-SU
$ zdump -v W-SU | tail
W-SU Sat Oct 24 22:59:59 2009 UTC = Sun Oct 25 02:59:59 2009 MSD isdst=1
gmtoff=14400
W-SU Sat Oct 24 23:00:00 2009 UTC = Sun Oct 25 02:00:00 2009 MSK isdst=0
gmtoff=10800
W-SU Sat Mar 27 22:59:59 2010 UTC = Sun Mar 28 01:59:59 2010 MSK isdst=0
gmtoff=10800
W-SU Sat Mar 27 23:00:00 2010 UTC = Sun Mar 28 03:00:00 2010 MSD isdst=1
gmtoff=14400
W-SU Sat Oct 30 22:59:59 2010 UTC = Sun Oct 31 02:59:59 2010 MSD isdst=1
gmtoff=14400
W-SU Sat Oct 30 23:00:00 2010 UTC = Sun Oct 31 02:00:00 2010 MSK isdst=0
gmtoff=10800
W-SU Sat Mar 26 22:59:59 2011 UTC = Sun Mar 27 01:59:59 2011 MSK isdst=0
gmtoff=10800
W-SU Sat Mar 26 23:00:00 2011 UTC = Sun Mar 27 03:00:00 2011 MSK isdst=0
gmtoff=14400
W-SU 9223372036854689407 = NULL
W-SU 9223372036854775807 = NULL
I also see this problem in my other server with 9.0:
select '2011-03-27 23:00:00'::timestamptz;
timestamptz
------------------------
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
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+04Bizarre. 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
$
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+04Bizarre. 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)
Ubuntu 10.04 LTS
Linux kevin-desktop 2.6.32-40-generic-pae #87-Ubuntu SMP
Mon Mar 5 21:44:34 UTC 2012 i686 GNU/Linux
PostgreSQL 9.2devel on i686-pc-linux-gnu,
compiled by gcc (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit
test=# set timezone to 'Europe/Moscow'; select '2011-03-27
23:00:00'::timestamptz;
SET
timestamptz
------------------------
2011-03-28 00:29:40+04
(1 row)
-Kevin
Import Notes
Resolved by subject fallback
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