Thread: Timezone fun (bugs and a request)

Timezone fun (bugs and a request)

From
Alvaro Herrera
Date:
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"



Re: Timezone fun (bugs and a request)

From
Tom Lane
Date:
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


Re: Timezone fun (bugs and a request)

From
Alvaro Herrera
Date:
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.



Re: Timezone fun (bugs and a request)

From
Tom Lane
Date:
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


Re: Timezone fun (bugs and a request)

From
Oliver Jowett
Date:
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


Re: Timezone fun (bugs and a request)

From
Tom Lane
Date:
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


Re: Timezone fun (bugs and a request)

From
Oliver Jowett
Date:
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


Re: Timezone fun (bugs and a request)

From
Tom Lane
Date:
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


Re: Timezone fun (bugs and a request)

From
"Zeugswetter Andreas SB SD"
Date:
>    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


Re: Timezone fun (bugs and a request)

From
Oliver Jowett
Date:
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


Re: Timezone fun (bugs and a request)

From
Bruce Momjian
Date:
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
 


Re: Timezone fun (bugs and a request)

From
Tom Lane
Date:
"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


Re: Timezone fun (bugs and a request)

From
Tom Lane
Date:
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


Re: Timezone fun (bugs and a request)

From
Greg Stark
Date:
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



Re: Timezone fun (bugs and a request)

From
Alvaro Herrera
Date:
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."



Re: Timezone fun (bugs and a request)

From
Oliver Jowett
Date:
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