Обсуждение: 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

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

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

Karsten D=FCsterloh <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=20
unified. Railways in Prussia used Berlin time from 1848 up to 1893, when=20
the time was changed by law:
http://de.wikipedia.org/wiki/Gesetz=5Fbetreffend=5Fdie=5FEinf%C3%BChrung=5F=
einer=5Feinheitlichen=5FZeitbestimmung
http://zeitzonen.net/html/geschichte.html

Best regards,

Curd

Karsten Düsterloh <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