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

On 28/07/06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Alistair Bayley" <alistair(at)abayley(dot)org> writes:
> > 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.
>
> That is strange, seeing that "GMT" is surely textually shorter. And the
> probing function does check as far back as 1916 (indeed back to 1904)
> so I don't see why it'd not notice the difference anyway. Could you
> trace through it (either with gdb, or add some debug elogs in pgtz.c)
> and see why it doesn't give you the right choice?

Hmm... I probably should be ashamed to admit it, but I have no
experience of gdb. Could you point me to some kind of guide to running
PostgreSQL in gdb? Is there a wiki-page or something similar? And, is
it reasonably straightforward under windows (I have MinGW/MSYS
installed).

Actually, looking at the code again, I can see what happens, I think.
This entry in win32_tzmap maps my GMT timezone to PG's Europe/Dublin
timezone:
{
"GMT Standard Time", "GMT Daylight Time",
"Europe/Dublin"
}, /* (GMT) Greenwich Mean Time : Dublin,
* Edinburgh, Lisbon, London */
(The first string is the Windows std timezone name, the second is the
daylight-savings timezone name, and the third is the pgsql timezone to
map to.)

So the server deliberately maps GMT to Europe/Dublin. From my POV this
is a dubious decision, but maybe there's a good reason for it.

> > 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?
>
> Any individual session can SET TIMEZONE to whatever it wants. The point
> here is just what the startup default is.

The server stores all timestamps at UTC, so then I guess the server
timezone is probably irrelevant, except to use as a default for client
sessions. Or is there another valid use for having a server timezone?
(as opposed to simply running the server at UTC)

> > Why do we not simply
> > use TZ on the client, instead of PGTZ?
>
> Why do you think the client machine is any more likely to have a correct
> setting of TZ than the server? I'd guess the opposite myself.

Well, if the client is in a different timezone from the server, then
using the server timezone as a session default will give you incorrect
conversions from UTC. Or have I got this quite wrong? Is there some
further documentation, or archived email discussions, I could read?
(I'd be surprised if this hasn't been discussed before)

Perhaps a sensible rule for client libs (libpq) might be: use PGTZ if
set, otherwise use TZ if set, otherwise probe the system clock like
the server does; if all alse fails then use the server timezone.

> I think you're confused about "Julian dates" vs "Julian days". The
> latter is just a term for counting from a specific epoch day sometime
> back in 4000-something BC. We use the Gregorian calendar though.

OK. I guess that's something which could be added to the docs; should
I raise that on the -docs list, or just update the online docs myself?

Alistair