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
