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
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 NAME GMTOFF RULES FORMAT [UNTIL]
Zone Europe/Berlin 0:53:28 - LMT 1893 Apr
1:00 C-Eur CE%sT 1945 May 24 2:00
1:00 SovietZone CE%sT 1946
1:00 Germany CE%sT 1980
1:00 EU 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
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 NAME GMTOFF RULES FORMAT [UNTIL]
Zone Europe/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
Karsten Düsterloh <pg-bugs-ml@tal.de> wrote
hubert depesz lubaczewski wrote:
Zone Europe/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
=?ISO-8859-1?Q?Karsten_D=FCsterloh?= <pg-bugs-ml@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 NAME GMTOFF RULES FORMAT [UNTIL]
Zone Europe/Berlin 0:53:28 - LMT 1893 Apr
1:00 C-Eur CE%sT 1945 May 24 2:00
1:00 SovietZone CE%sT 1946
1:00 Germany CE%sT 1980
1:00 EU 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