Re: What's special about 1916-10-01 02:25:20? Odd jump in internal timestamptz representation

On 26/07/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Alistair Bayley" <alistair(at)abayley(dot)org> writes:
> > The first line of output puzzles me: why is '1916-10-01 02:25:20'
> > 2627158159 seconds before 2000-01-01, while '1916-10-01 02:25:21' is
> > 2627156080 before; a difference of 2080 seconds, or 34m:40s.
>
> What timezone are you testing in?
>
> Perusing the zic database makes me think it might be Europe/Dublin,
> because there's a DST rule with a related breakpoint:

You are correct. "show TimeZone" and "select * from pg_settings" both
indicate Europe/Dublin.

I was puzzled as to why it is set to Dublin when my machine's Time
Zone is GMT. I saw in the docs that in the absense of an entry in the
.conf file or a TZ environment variable results in a guess; this seems
to be the cause here. I see in
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/timezone/pgtz.c?rev=1.44
that the rule seems to prefer shorter names when there's a tie, and
win32_tzmap has Europe/Dublin as the shortest entry in the GMT
section, so perhaps that's the reason... the best choice for me would
have been GMT.

I'll set the server timezone in postgresql.conf to GMT or UTC.

Obviously I've been burnt by timezone conversion... I'll have to read
more about this. Thanks for the link to the timezone data file.

Is it possible for a client to have a different time zone from the
server, or is the only time zone we consider the server time zone? The
latter I think, as the default time zone for a session is the server
time zone (in the absense of a PGTZ variable). Why do we not simply
use TZ on the client, instead of PGTZ?

Also, is it correct for the docs to state that Julian dates are used?
The docs state that the Julian calendar has a year length of 365.2425
days, which is not correct, I think. According to Wikipedia, the
Julian calendar has a year length of 365.25 days, while the Gregorian
calendar has a year length of 365.2425 days. I suspect that the actual
calendar implemented is the Gregorian, and the docs are wrong.

http://en.wikipedia.org/wiki/Julian_calendar
http://en.wikipedia.org/wiki/Gregorian_calendar

Thanks for your help,
Alistair