A few notes on timezones

Dear all,

please excuse me for posting out of the blue (I am no longer
subscribed) but I have been asked by my colleagues to send a message
since I've pretty much been hacking at this problem all day.

To summarise the issue briefly we were very confused regarding the SET
TIMEZONE command which behaved differently on Linux and Tru64 Unix
4.0F. We immediately blamed Postgres, as one normally does, and then
decided that since RC1 is out it would be better if we actually worked
out what the really issue was.

As usual a good RTFM session provided the answer. The idea of this
message is to provide a sort of "tutorial" on how it apparently
timezone changes are handled according to POSIX.1 and XPG.4.

SET TIMEZONE is dealt with in src/backend/commands/variable.c:357 and
the following short program[1]:

/* tzset-test.c */

#include <stdio.h>
#include <time.h>
#include <stdlib.h>

main(int argc, char **argv)
{
char *tzone="TZ=GMT0";
extern long timezone;

if ( argc > 1)
putenv(argv[1]);
else
putenv(tzone);
tzset();
printf("daylight=%d\ntimezone=%ld\ntzname[0]=%s\ntzname[1]=%s\n",
daylight, timezone,
(tzname[0] ? tzname[0] : "NULL"),
(tzname[1] ? tzname[1] : "NULL"));
exit(0);
}

simulates the procedure used to change the timezone in
parse_timezone(). In a few words what needs to be done is that the
environment variable TZ is set to the required value and this is
"imported" back into the program by using tzset().

Now, the simple issue we were facing was that setting the timezone to
GMT worked under Linux but not under Tru64 Unix. In particular
someone on this mailing list replied something along the lines of
"well, you need to set it to something which the OS recognises". It
turned out that the statement is true but in a different sense than
what we had expected. We were of the mistaken belief that the
timezone had to be set to something known in /etc/zoneinfo (Tru64 Unix
notation), i.e. one of:

Australia GMT GMT+7 GMT-6 GMT4 Japan Singapore
Belfast GMT+0 GMT+8 GMT-7 GMT5 Libya SystemV
Brazil GMT+1 GMT+9 GMT-8 GMT6 London Turkey
CET GMT+10 GMT-0 GMT-9 GMT7 MET UCT
Canada GMT+11 GMT-1 GMT0 GMT8 Mexico US
Chile GMT+12 GMT-10 GMT1 GMT9 NZ UTC
Cuba GMT+13 GMT-11 GMT10 Greenwich NZ-CHAT Universal
Dublin GMT+2 GMT-12 GMT11 Hongkong Navajo W-SU
EET GMT+3 GMT-2 GMT12 Iceland PRC WET
Egypt GMT+4 GMT-3 GMT13 Iran Poland Zulu
Factory GMT+5 GMT-4 GMT2 Israel ROC localtime
GB-Eire GMT+6 GMT-5 GMT3 Jamaica ROK sources

It actually turns out that this is not the case. The _correct_ value,
i.e. the one mandated by the tzset(3) man page and, according to
Mr. Digital,

``
Interfaces documented on this reference page conform to industry standards
as follows:

tzset(): POSIX.1, XPG4, XPG4-UNIX
''

is in fact not "GMT" or "Iceland" but a string of the form:

``
When TZ appears in the environment and its value is not a null string, the
value has one of three formats:

:
:pathname
stdoffset[dst[offset] [,start[/time],end[/time]]]
''

where ':' means UTC, ':pathname' sends you to the zoneinfo file and
the last one is the string which should be used. In particular, where
Linux accepts GMT and reads it to be GMT0, under Tru64 Unix the
correct behaviour _requires_ the use of GMT0.

Examples of this behaviour are (local timezone EET DST, GMT+3):

[Tru64 Unix 4.0F (and 4.0G)]
./tzset-test TZ=GMT
daylight=1
timezone=-7200
tzname[0]=EET
tzname[1]=EET DST

[Debian GNU/Linux 2.2 (Kernel 2.2.18, glibc 2.1.3)]
./tzset-test TZ=GMT
daylight=0
timezone=0
tzname[0]=GMT
tzname[1]=GMT

whereas the POSIXly "correct" (the use of quotes will become apparent
later) setting of TZ=GMT0 gives the "expected" result:

[Tru64 Unix 4.0F (and 4.0G)]
./tzset-test TZ=GMT0
daylight=0
timezone=0
tzname[0]=GMT
tzname[1]=

[Debian GNU/Linux 2.2 (Kernel 2.2.18, glibc 2.1.3)]
./tzset-test TZ=GMT0
daylight=0
timezone=0
tzname[0]=GMT
tzname[1]=GMT

As you can imagine the above discrepancy, seen from within Postgres
but not tested separately, had driven us to despair for our application
which "localised" times depending on the remote user location.

Now, this might be all closed but as a matter of fact we went a little
further and discovered that, as long as you use the POSIXly defined
format you can specify the timezones to be anything you want! For
example[2]:

./tzset-test TZ=PIPPO0PLUTO-2
daylight=1
timezone=0
tzname[0]=PIPPO
tzname[1]=PLUTO

is perfectly valid. Not only, unless you specify also the "change
dates" for DST, as specified earlier, you run the risk of wrong
conversions. Any timezone used at the moment will always be assumed
to be in DST if your local timezone is in DST! So, the call
./tzset-test 'TZ=CET-1CET DST-2' is correct (Central European Time is
1 hr East of GMT and 2 hrs East of GMT during DST) and changes to DST
correctly only because EET on the local server changes to EET EST at
the same time. So far the only "fix" we have is to write the changes
in full gory detail by taking them from the sources in
/etc/zoneinfo/sources (Tru64 Unix location).

Now, possibly a small buglet. The external variable "timezone"
appears to be corrupted somewhere in Postgres. For reasons which are
not entirely clear to me when we added some debugging lines to
parse_timezone() in an attempt to resolve the issue, the value of
timezone was never the expected one. After adding an explicit

extern long timezone;

to the function it was still happily there and values printed are
obviously wrong (compare with the -7200 in one of the examples above,
it is meant to be the number of seconds west of GMT, negative numbers
indicating east).

As an example, the log output from the "patched" version of variable.c
is:

New parse_timezone(value) call
defaultTZ = 0 (NULL)
defaultTZ = 0 (NULL)
tzbuf = 14004efe0 (TZ=GMT0)
timezone = 3458201564533694440, dst = 0, tzname=GMT/
End of parse_timezone()

and the value of timezone is clearly rather incorrect.

I have been unable to work out if this is because timezone is shadowed
(the cause of my retracted post to tru64-unix-managers) or not as I
cannot find anything which obviously is shadowing it (the macro
TIMEZONE_GLOBAL appears to be used consistently and the only
occurrence of a "bool timezone" is within a struct).

Please excuse the length of this post but given the amount of time
spent today working all this out we all thought it made sense to
document our effort.

Ciao,

Arrigo

[1] You might recognise this code from my erroneous posting to the
tru64-unix-managers which claimed the bug was in their library...
[2] The names are the italian equivalent of foo and bar (see "The
New Hacker's Dictionary" by Eric Raymond).

--
Arrigo Triulzi <arrigo(at)albourne(dot)com>
Albourne Partners Ltd. - London, UK