Skip site navigation (1) Skip section navigation (2)

BUG #6605: wrong type cast from timestamp to timestamptz

From: eshkinkot(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6605: wrong type cast from timestamp to timestamptz
Date: 2012-04-21 07:11:50
Message-ID: E1SLUU2-0004nB-1c@wrigleys.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      6605
Logged by:          Sergey Burladyan
Email address:      eshkinkot(at)gmail(dot)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)



Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2012-04-21 17:49:55
Subject: Re: BUG #6605: wrong type cast from timestamp to timestamptz
Previous:From: ljwilsonDate: 2012-04-20 15:23:07
Subject: Re: BUG #6204: Using plperl functions generate crash

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group