Thread: 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"
Alvaro Herrera <alvherre@dcc.uchile.cl> 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
On Sun, May 23, 2004 at 04:58:29PM -0400, Tom Lane wrote: > Alvaro Herrera <alvherre@dcc.uchile.cl> 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.
Alvaro Herrera <alvherre@dcc.uchile.cl> 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
Tom Lane wrote: > Alvaro Herrera <alvherre@dcc.uchile.cl> 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. > oliver@flood:~$ ls -l /etc/localtime > lrwxrwxrwx 1 root root 36 Feb 2 17:08 /etc/localtime -> /usr/share/zoneinfo/Pacific/Auckland > oliver@flood:~$ date > Tue May 25 15:14:53 NZST 2004 > oliver@flood:~$ 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
Oliver Jowett <oliver@opencloud.com> 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
Tom Lane wrote: > Oliver Jowett <oliver@opencloud.com> 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
Oliver Jowett <oliver@opencloud.com> 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
> 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
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
Oliver Jowett wrote: > Tom Lane wrote: > > Oliver Jowett <oliver@opencloud.com> 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 pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> 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
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
Oliver Jowett <oliver@opencloud.com> 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
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."
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