Re: [HACKERS] Timezone fun (bugs and a request)
LOG: database system was shut down at 2004-05-25 15:15:44 GMT-12 For comparison, 7.4.1 on the same system says: LOG: database system was shut down at 2004-05-25 16:03:43 NZST Can we keep the zic database convention unchanged but change the display format in the logs to be consistent with the SQL conventions? I think what was meant is, that if it displays GMT+-something it should convert the sign. Is that zic filename exposed anywhere else ? It is common practice that + is East and - is West, no ? Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Timezone fun (bugs and a request)
Zeugswetter Andreas SB SD wrote: LOG: database system was shut down at 2004-05-25 15:15:44 GMT-12 For comparison, 7.4.1 on the same system says: LOG: database system was shut down at 2004-05-25 16:03:43 NZST Can we keep the zic database convention unchanged but change the display format in the logs to be consistent with the SQL conventions? I think what was meant is, that if it displays GMT+-something it should convert the sign. Is that zic filename exposed anywhere else ? It is common practice that + is East and - is West, no ? Yes, exactly. Regardless of how the timezone is handled internally, showing 12-hours-east as GMT-12 in logs is horribly confusing. -O ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Timezone fun (bugs and a request)
Oliver Jowett wrote: Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: Also, unless I'm missing something, shouldn't Chile (Alvaro's timezone?) be behind GMT (GMT-something) not ahead of it (GMT+something)? Part of the confusion here is that the zone names in the zic database follow POSIX rules: plus is west of Greenwich. AFAICS it's doing the right thing in selecting Etc/GMT-12 for you. Ew! That's disgusting! What possessed POSIX to do this the opposite way to pretty much everything else? You want ugly, look at how we find the system timezone --- we scan forward for 14 months looking at the timezone abbreviations returned by the operating system. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Timezone fun (bugs and a request)
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: It is common practice that + is East and - is West, no ? The SQL standard says that. The POSIX standard says the opposite. Most of the Unixen I'm familiar with follow POSIX when choosing time zone names. The zic database is in itself a de facto standard (it's used verbatim in glibc, I believe, and on several non-glibc platforms). So I'm rather hesitant to make any unilateral changes in it. I would like to think that we will be able to just drop in any updates that occur from time to time in the zone database files. Politicians being what they are, you can be certain there will be changes to track. Also, it's worth pointing out here that falling back to Etc/GMT+/-n is intended to be just that, a last-ditch fallback that won't be seen in normal practice. We still need to do some more work on identify_system_timezone() to make that happen outside North America, but I would like to think that average users will never see these zones except by deliberate selection. So I think it's not worth getting worked up about what the sign convention is. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timezone fun (bugs and a request)
I wrote: Also, it's worth pointing out here that falling back to Etc/GMT+/-n is intended to be just that, a last-ditch fallback that won't be seen in normal practice. We still need to do some more work on identify_system_timezone() to make that happen outside North America, but I would like to think that average users will never see these zones except by deliberate selection. BTW, as of an hour or so ago, identify_system_timezone is a bit smarter than before. Please try it and see if it gets it right on your machine. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Timezone fun (bugs and a request)
Oliver Jowett [EMAIL PROTECTED] writes: Yes, exactly. Regardless of how the timezone is handled internally, showing 12-hours-east as GMT-12 in logs is horribly confusing. Well, uh, you could always just pretend it was really 12-hours-west... That doesn't help people in other time zones though :) -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Timezone fun (bugs and a request)
On Tue, May 25, 2004 at 03:01:24PM -0400, Tom Lane wrote: I wrote: Also, it's worth pointing out here that falling back to Etc/GMT+/-n is intended to be just that, a last-ditch fallback that won't be seen in normal practice. We still need to do some more work on identify_system_timezone() to make that happen outside North America, but I would like to think that average users will never see these zones except by deliberate selection. BTW, as of an hour or so ago, identify_system_timezone is a bit smarter than before. Please try it and see if it gets it right on your machine. Works here perfectly, thank you. I changed the system date to see if it worked on DST too, and it does. I can also attest that it takes a very short time to find the correct timezone, and this is a very slow machine (Intel Celeron 533). -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) No renuncies a nada. No te aferres a nada. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Timezone fun (bugs and a request)
Tom Lane wrote: BTW, as of an hour or so ago, identify_system_timezone is a bit smarter than before. Please try it and see if it gets it right on your machine. Looks good: LOG: database system was shut down at 2004-05-26 12:43:55 NZST test=# select now(); now -- 2004-05-26 12:44:30.00082+12 (1 row) -O ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Timezone fun (bugs and a request)
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: First I initdb'd without TZ set. So every time I start the server I get LOG: could not recognize system timezone, defaulting to Etc/GMT-4 HINT: You can specify the correct timezone in postgresql.conf. I've fixed the minor issue here, which is that the sign is backwards --- it ought to select Etc/GMT+4. The larger issue is that it's not recognizing your system timezone because the only name it can cons up for the zone is CLT4CLST, which doesn't work because it has the wrong DST rules. (I think it's just luck that it realizes that, actually :-(). With a freshly updated CVS tree I get the wrong sign on the timezone here: LOG: could not recognize system timezone, defaulting to Etc/GMT-12 HINT: You can specify the correct timezone in postgresql.conf. LOG: database system was shut down at 2004-05-25 15:15:44 GMT-12 My timezone is NZST which is GMT+12. TZ is not set. This is a Debian box with libc-2.3.2. [EMAIL PROTECTED]:~$ ls -l /etc/localtime lrwxrwxrwx1 root root 36 Feb 2 17:08 /etc/localtime - /usr/share/zoneinfo/Pacific/Auckland [EMAIL PROTECTED]:~$ date Tue May 25 15:14:53 NZST 2004 [EMAIL PROTECTED]:~$ date +'%c %z' Tue May 25 15:30:11 2004 +1200 Also, unless I'm missing something, shouldn't Chile (Alvaro's timezone?) be behind GMT (GMT-something) not ahead of it (GMT+something)? -O ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Timezone fun (bugs and a request)
Oliver Jowett [EMAIL PROTECTED] writes: Also, unless I'm missing something, shouldn't Chile (Alvaro's timezone?) be behind GMT (GMT-something) not ahead of it (GMT+something)? Part of the confusion here is that the zone names in the zic database follow POSIX rules: plus is west of Greenwich. AFAICS it's doing the right thing in selecting Etc/GMT-12 for you. Have you checked the actual time values reported by the server to see if they look okay? I'm a bit hesitant to reverse the sign convention in the zic database to agree with SQL conventions --- that seems certain to lead to even more confusion. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Timezone fun (bugs and a request)
Tom Lane wrote: Oliver Jowett [EMAIL PROTECTED] writes: Also, unless I'm missing something, shouldn't Chile (Alvaro's timezone?) be behind GMT (GMT-something) not ahead of it (GMT+something)? Part of the confusion here is that the zone names in the zic database follow POSIX rules: plus is west of Greenwich. AFAICS it's doing the right thing in selecting Etc/GMT-12 for you. Ew! That's disgusting! What possessed POSIX to do this the opposite way to pretty much everything else? Have you checked the actual time values reported by the server to see if they look okay? Indeed, the timezone is actually correct: oliver=# select now(); now --- 2004-05-25 16:08:05.688408+12 (1 row) What confused me is that the times in the log don't follow the SQL-and-everything-else convention: LOG: database system was shut down at 2004-05-25 15:15:44 GMT-12 For comparison, 7.4.1 on the same system says: LOG: database system was shut down at 2004-05-25 16:03:43 NZST and apache says: 127.0.0.1 - - [25/May/2004:16:06:16 +1200] GET / HTTP/1.0 200 4110 - Wget/1.9.1 I'm a bit hesitant to reverse the sign convention in the zic database to agree with SQL conventions --- that seems certain to lead to even more confusion. Can we keep the zic database convention unchanged but change the display format in the logs to be consistent with the SQL conventions? -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Timezone fun (bugs and a request)
Oliver Jowett [EMAIL PROTECTED] writes: What confused me is that the times in the log don't follow the SQL-and-everything-else convention: LOG: database system was shut down at 2004-05-25 15:15:44 GMT-12 For comparison, 7.4.1 on the same system says: LOG: database system was shut down at 2004-05-25 16:03:43 NZST Right now, to get that you need to set a TimeZone setting that will select the appropriate New Zealand time zone by name. (I think 'Pacific/Auckland' is the one you want.) Hopefully we will find some way of deducing the correct time zone name more automatically, but right now it's a work in progress. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Timezone fun (bugs and a request)
I'm looking at the new timezone support. First I initdb'd without TZ set. So every time I start the server I get LOG: could not recognize system timezone, defaulting to Etc/GMT-4 HINT: You can specify the correct timezone in postgresql.conf. Obviously the setup is wrong because DST doesn't work: alvherre=# select '2004-03-13 10:00:00'::timetz; timetz - 10:00:00+04 (1 fila) alvherre=# select '2004-03-14 10:00:00'::timetz; timetz - 10:00:00+04 (1 fila) alvherre=# set TimeZone to 'Chile/Continental'; SET alvherre=# select '2004-03-13 10:00:00'::timetz; timetz - 10:00:00-03 (1 fila) alvherre=# select '2004-03-14 10:00:00'::timetz; timetz - 10:00:00-04 (1 fila) Note I get +4 on the default zone and -4 on the correct zone. I think this is a bug. So I went and set it in postgresql.conf, timezone = 'Chile/Continental' After this, DST works correctly, but I continue to receive the LOG message above. I think it should be supressed. I then changed postgresql.conf to read timezone = unknown (the difference with the original setting is that the line isn't commented). The server now behaves different; the timezone is set to GMT rather than being guessed from system settings. I think they should work the same. I also want to be able to specify a non-default timezone and get a time with the correct displacement. CLT is abbreviation for Chile/Continental, and CLST is the summer timezone. alvherre=# select '10:00:00'::time at time zone 'CLT'; timezone - 10:00:00-04 (1 fila) alvherre=# select '10:00:00'::time at time zone 'CLST'; timezone - 11:00:00-03 (1 fila) alvherre=# select '10:00:00'::time at time zone 'Chile/Continental'; ERROR: el huso horario chile/continental no es reconocido I would like to get the time in the corresponding zone, without me having to know in advance whether I'm in current DST or not. Is this possible? For example I want to know what's the current time in 'Europe/Madrid'. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Timezone fun (bugs and a request)
Alvaro Herrera [EMAIL PROTECTED] writes: First I initdb'd without TZ set. So every time I start the server I get LOG: could not recognize system timezone, defaulting to Etc/GMT-4 HINT: You can specify the correct timezone in postgresql.conf. So what is your system timezone anyway (and what's the platform)? I then changed postgresql.conf to read timezone = unknown (the difference with the original setting is that the line isn't commented). The server now behaves different; the timezone is set to GMT rather than being guessed from system settings. I think they should work the same. Hmm, that's strange. I thought they would work the same. Will look into it. alvherre=# select '10:00:00'::time at time zone 'Chile/Continental'; ERROR: el huso horario chile/continental no es reconocido This is functionality that never has existed. We have perhaps some chance of coding it now, but it's not a trivial bug fix. The main problem is that the timezone library API is still based around a global tzset() setting. We need it to be able to deal with timezone definitions that are loaded (hopefully only once) but not selected as the program-wide default. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Timezone fun (bugs and a request)
On Sun, May 23, 2004 at 04:58:29PM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: First I initdb'd without TZ set. So every time I start the server I get LOG: could not recognize system timezone, defaulting to Etc/GMT-4 HINT: You can specify the correct timezone in postgresql.conf. So what is your system timezone anyway (and what's the platform)? This is Linux 2.6 with glibc 2.3.3. My timezone is America/Santiago (or Chile/Continental which is the same). The timezone is set via /etc/localtime having the content of the timezone file (not as a symlink as it used to be some time ago). The TZ variable isn't set. alvherre=# select '10:00:00'::time at time zone 'Chile/Continental'; ERROR: el huso horario chile/continental no es reconocido This is functionality that never has existed. Right, I know because I tried to use it with 7.4 some time ago. This part was more a feature request than a bug report. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) XML! Exclaimed C++. What are you doing here? You're not a programming language. Tell that to the people who use me, said XML. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Timezone fun (bugs and a request)
Alvaro Herrera [EMAIL PROTECTED] writes: First I initdb'd without TZ set. So every time I start the server I get LOG: could not recognize system timezone, defaulting to Etc/GMT-4 HINT: You can specify the correct timezone in postgresql.conf. I've fixed the minor issue here, which is that the sign is backwards --- it ought to select Etc/GMT+4. The larger issue is that it's not recognizing your system timezone because the only name it can cons up for the zone is CLT4CLST, which doesn't work because it has the wrong DST rules. (I think it's just luck that it realizes that, actually :-(). Magnus and I had talked off-list about a smarter routine to recognize the system timezone without a TZ setting, but the only idea we've had is to grovel through each and every timezone file in the zic database, which doesn't seem real appealing. Note I get +4 on the default zone and -4 on the correct zone. I think this is a bug. Right, that much is fixed anyway. After this, DST works correctly, but I continue to receive the LOG message above. I think it should be supressed. Fixed. timezone = unknown The server now behaves different; the timezone is set to GMT rather than being guessed from system settings. I think they should work the same. Fixed. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
