Re: 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(at)dcc(dot)uchile(dot)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(at)dcc(dot)uchile(dot)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(at)dcc(dot)uchile(dot)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(at)dcc(dot)uchile(dot)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(at)flood:~$ ls -l /etc/localtime
> lrwxrwxrwx 1 root root 36 Feb 2 17:08 /etc/localtime -> /usr/share/zoneinfo/Pacific/Auckland
> oliver(at)flood:~$ date
> Tue May 25 15:14:53 NZST 2004
> oliver(at)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(at)opencloud(dot)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(at)opencloud(dot)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(at)opencloud(dot)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

Oliver Jowett wrote:
> Tom Lane wrote:
> > Oliver Jowett <oliver(at)opencloud(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073