A few notes on timezones
От | Arrigo Triulzi |
---|---|
Тема | A few notes on timezones |
Дата | |
Msg-id | 15040.57233.197701.455081@umbra.northsea.sevenseas.org обсуждение исходный текст |
Список | pgsql-hackers |
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 theirlibrary... [2] The names are the italian equivalent of foo and bar (see "The New Hacker's Dictionary" by Eric Raymond). -- Arrigo Triulzi <arrigo@albourne.com> Albourne Partners Ltd. - London, UK
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Maurizio OrtolanДата:
Сообщение: Error in the date field (with NULL value...).Thanks!