Re: Strange time zone +00:53:28

=?ISO-8859-1?Q?Karsten_D=FCsterloh?= <pg-bugs-ml(at)tal(dot)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(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs