[BUGS] Strange time zone +00:53:28
Hi! Recently, we switched from Pg 8.3 to Pg 9.1, using Pg9's pg_upgrade to migrate our cluster. The cluster itself is running on a Debian 6 machine with timezone Europe/Berlin, which means UTC+02 currently. Under Pg 8.3, we used the timestamp 0001-01-01 00:00:00+01 as an easy-to-remember marker for 'dunno, but predates any usual business dates' for fields of type timestamp with time zone. With Pg 9.1, these timestamps now appear as 0001-12-31 23:53:28+00:53:28 BC (using a current psql shell in both cases). If I try to reset the timestamp, eg by update testtable set ts='0001-01-01'; the result is 0001-01-01 00:00:00+00:53:28 Specifying the time explicitly update testtable set ts='0001-01-01 00:00:00+01:00:00'; results in 0001-12-31 23:53:28+00:53:28 BC again, hence I'd suspect that somehow the textual representation of such timestamps is broken?! Any ideas? Is this a known bug (I didn't find), maybe fixed in 9.2? Karsten -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Strange time zone +00:53:28
On pią, maj 10, 2013 at 10:34:02 +0200, Karsten Düsterloh wrote: Under Pg 8.3, we used the timestamp 0001-01-01 00:00:00+01 as an easy-to-remember marker for 'dunno, but predates any usual business dates' for fields of type timestamp with time zone. Why didn't you use -infinity for this? Any ideas? Is this a known bug (I didn't find), maybe fixed in 9.2? It's not a bug. Berlin timezone that far away has such not-round offset. In time zone database you can find: # Zone NAMEGMTOFF RULES FORMAT [UNTIL] ZoneEurope/Berlin 0:53:28 - LMT 1893 Apr 1:00C-Eur CE%sT 1945 May 24 2:00 1:00 SovietZone CE%sT 1946 1:00Germany CE%sT 1980 1:00EU CE%sT Best regards, depesz -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Strange time zone +00:53:28
hubert depesz lubaczewski wrote: Under Pg 8.3, we used the timestamp 0001-01-01 00:00:00+01 as an easy-to-remember marker for 'dunno, but predates any usual business dates' for fields of type timestamp with time zone. Why didn't you use -infinity for this? Back when that value was chosen, we had some legacy software around which did not handle that value well. Definitely worth reevaluating. OTOH, Delphi4 + Win98 + ODBC is definitely choking on '0001-12-31 23:53:28+00:53:28 BC'. ;-) It's not a bug. Berlin timezone that far away has such not-round offset. In time zone database you can find: # Zone NAMEGMTOFF RULES FORMAT [UNTIL] ZoneEurope/Berlin 0:53:28 - LMT 1893 Apr Thanks! That does explain the number as such (I somehow only found the Sicily stuff mentioned further down in the time zone database file), why it's popping up now and that's just a display problem. The question remains, though, where this special value comes from … Karsten -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Strange time zone +00:53:28
Karsten Düsterloh pg-bugs...@tal.de wrote hubert depesz lubaczewski wrote: ZoneEurope/Berlin 0:53:28 - LMT 1893 Apr The question remains, though, where this special value comes from ? That was probably the local time at Berlin, with 12:00 the moment when the sun was highest. With the advent of the railways, time started to get unified. Railways in Prussia used Berlin time from 1848 up to 1893, when the time was changed by law: http://de.wikipedia.org/wiki/Gesetz_betreffend_die_Einf%C3%BChrung_einer_einheitlichen_Zeitbestimmung http://zeitzonen.net/html/geschichte.html Best regards, Curd
Re: [BUGS] Strange time zone +00:53:28
=?ISO-8859-1?Q?Karsten_D=FCsterloh?= pg-bugs...@tal.de writes: Under Pg 8.3, we used the timestamp 0001-01-01 00:00:00+01 as an easy-to-remember marker for 'dunno, but predates any usual business dates' for fields of type timestamp with time zone. Have you considered using '-infinity'? With Pg 9.1, these timestamps now appear as 0001-12-31 23:53:28+00:53:28 BC This is not a bug. I refer you to the IANA timezone database's entry for Europe/Berlin: # Zone NAMEGMTOFF RULES FORMAT [UNTIL] ZoneEurope/Berlin 0:53:28 - LMT 1893 Apr 1:00C-Eur CE%sT 1945 May 24 2:00 1:00 SovietZone CE%sT 1946 1:00Germany CE%sT 1980 1:00EU CE%sT which says that timekeeping before April 1893 was done according to local mean solar time, 53:28 east of Greenwich; so a timestamp specified as midnight GMT+1 comes out as 23:53:28 local time. Now, I agree that it's somewhat debatable to extend that rule clear back to 1 AD; but it's more sensible than believing that local time would ever have been taken as exactly GMT+1 before the days of standardized timezones. The only reason 8.3 and before didn't do what you're seeing is they were incapable of applying timezone rules outside the range of 32-bit time_t (ie, back to about 1901). We fixed that code to be 64-bit, and now it does what the timezone definition says. If you're inextricably wedded to using '0001-01-01 00:00:00+01', you might consider building yourself a custom timezone database that has an entry defined the way you want. But personally I'd recommend changing to something less randomly chosen. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
