Re: Timestamp with time zone 'negative' problem - Mailing list pgsql-general

From Tom Lane
Subject Re: Timestamp with time zone 'negative' problem
Date
Msg-id 9315.1327072850@sss.pgh.pa.us
Whole thread Raw
In response to Timestamp with time zone 'negative' problem  ("Prodan, Andrei" <Andrei.Prodan@awinta.com>)
List pgsql-general
"Prodan, Andrei" <Andrei.Prodan@awinta.com> writes:
> I have a DB in which items which are 'always valid' have a from_date of
> 19000101 000000+1 (Europe/Berlin)
> When i try to restore the same DB to (Europe/Bucharest), instead of
> 19000101 000000+2, the timestamp becomes "1900-01-01 00:44:24+01:44:24"
> which is ... strange.

No, not particularly.  According to the Olson timezone database,
Bucharest didn't adopt standard time (that is, an integral-hours offset
from GMT) until 1931.  Before that, local midnight was true local
midnight, and they are 1:44:24 east of Greenwich, so that is what
"midnight of 1900/01/01" means in that zone.

The only reason your code fails to fail in Europe/Berlin zone is that
Berlin adopted standard time in 1893, so that midnight of 1900/01/01
is already an exact offset from GMT in that zone.

Personally, I'd suggest getting away from the magic number.  Consider
using NULL or -infinity instead of an arbitrary date.  Failing that,
don't use a place-based zone with all the historical baggage that
those entail, but something fixed like 'GMT+1'.

            regards, tom lane

pgsql-general by date:

Previous
From: David Johnston
Date:
Subject: Re: Immutable function with bind value
Next
From: George Younan
Date:
Subject: