Thread: Allowed timezone values

Allowed timezone values

From
Bruce Momjian
Date:
I am playing with our allowed timezone settings and saw a few strange
things.  It understands "EST5EDT", but how does it understand "XYT5ABT"?test=> set timezone = 'XYT5ABT';SET
test=> SELECT current_timestamp;             now------------------------------ 2005-10-13 12:48:39.59164-04(1 row)
test=> SELECT current_timestamp + '3 months';           ?column?------------------------------- 2006-01-13
12:48:32.765068-05(1row)
 
(Shouldn't current_timestamp's label be "current_timestamp", and not
"now"?)

Also, JST doesn't work anymore, but JST9 does.  JST has no daylight
savings time values, so it is a valid timezone setting, while EST is
not:
test=> set timezone = 'JST';ERROR:  unrecognized time zone name: "JST"
test=> set timezone = 'JST9';SET

--  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: Allowed timezone values

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I am playing with our allowed timezone settings and saw a few strange
> things.  It understands "EST5EDT", but how does it understand "XYT5ABT"?

Because the code in src/timezone does what the POSIX standard says it
must do.  The relevant man page on my HPUX box says
    TZ          TZ sets time zone information.  TZ can be set using the                format:
                        [:]STDoffset[DST[offset][,rule]]
                   where:
                      STD and DST Three or more bytes that designate the                                  standard time
zone(STD) and summer (or                                  daylight-savings) time zone (DST) STD is
           required.  If DST is not specified,                                  summer time does not apply in this
                           locale.  Any characters other than                                  digits, comma (,), minus
(-),plus (+),                                  or ASCII NUL are allowed.
 
                      offset      offset is the value that must be added                                  to local time
toarrive at Coordinated                                  Universal Time (UTC).  Offset is of the
         form :
 
                                       hh[:mm[:ss]]
        ... etc etc ...

> Also, JST doesn't work anymore, but JST9 does.

JST9 is valid per the POSIX rules.  JST isn't listed as a zone name in
the zic database, so it's not valid.  (Try "Japan" instead.)
        regards, tom lane


Re: Allowed timezone values

From
Tatsuo Ishii
Date:
> > Also, JST doesn't work anymore, but JST9 does.
> 
> JST9 is valid per the POSIX rules.  JST isn't listed as a zone name in
> the zic database, so it's not valid.  (Try "Japan" instead.)

Shall I add JST to our zic database? It's quite confusing that
"2005-10-14 12:00 JST" is allowed while ""SET TIME ZONE 'JST'" is not,
and "2005-10-14 12:00 Japan" is NOT allowed while ""SET TIME ZONE
'Japan'" is OK. Note that there's no daylight-saving time in Japan (at
this point).
--
SRA OSS, Inc. Japan
Tatsuo Ishii


Re: Allowed timezone values

From
Bruce Momjian
Date:
Tatsuo Ishii wrote:
> > > Also, JST doesn't work anymore, but JST9 does.
> > 
> > JST9 is valid per the POSIX rules.  JST isn't listed as a zone name in
> > the zic database, so it's not valid.  (Try "Japan" instead.)
> 
> Shall I add JST to our zic database? It's quite confusing that
> "2005-10-14 12:00 JST" is allowed while ""SET TIME ZONE 'JST'" is not,
> and "2005-10-14 12:00 Japan" is NOT allowed while ""SET TIME ZONE
> 'Japan'" is OK. Note that there's no daylight-saving time in Japan (at
> this point).

I have to say, I am a little confused too.  First, why does JST work but
XST does not?  Where does it get the JST from?  The database?
test=> select '2005-10-14 12:00 JST'::timestamp with time zone;      timestamptz------------------------ 2005-10-13
23:00:00-04(1row)test=> select '2005-10-14 12:00 XST'::timestamp with time zone;ERROR:  invalid input syntax for type
timestampwith time zone: "2005-10-14 12:00 XST"
 

And this is Tatsuo's complaint:test=> select '2005-10-14 12:00 Asia/Tokyo'::timestamp with time zone;ERROR:  invalid
inputsyntax for type timestamp with time zone: "2005-10-14 12:00 Asia/Tokyo"test=> set timezone = 'Asia/Tokyo';SET
 

And this:test=> set timezone = 'JST';ERROR:  unrecognized time zone name: "JST"test=> set timezone = 'JST9';SETtest=>
select'2005-10-14 12:00 JST9'::timestamp with time zone;ERROR:  invalid input syntax for type timestamp with time zone:
"2005-10-1412:00 JST9"
 

I assume it is related to these two TODO entries:
       o Merge hardwired timezone names with the TZ database; allow either         kind everywhere a TZ name is
currentlytaken       o Allow customization of the known set of TZ names (generalize the         present
australian_timezoneshack)
 

--  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: Allowed timezone values

From
Tom Lane
Date:
Tatsuo Ishii <ishii@sraoss.co.jp> writes:
> Shall I add JST to our zic database?

No.  We have to update that from the upstream database every release;
maintaining our own private mods is not acceptable.  If you want JST
to be recognized as a zic timezone, go lobby the upstream maintainers
for it.
        regards, tom lane


Re: Allowed timezone values

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I assume it is related to these two TODO entries:

>         o Merge hardwired timezone names with the TZ database; allow either
>           kind everywhere a TZ name is currently taken

Yes, the point here is that the datetime token table has a bunch of
hardwired zone names --- actually not so much timezone names as names
for specific GMT offsets (eg, it has EST and EDT but not EST5EDT).
We need to think about how to generalize that facility and merge it with
the zic stuff.  This is not something that's going to get fixed for 8.1,
however.
        regards, tom lane


Re: Allowed timezone values

From
Tatsuo Ishii
Date:
> Tatsuo Ishii <ishii@sraoss.co.jp> writes:
> > Shall I add JST to our zic database?
> 
> No.  We have to update that from the upstream database every release;
> maintaining our own private mods is not acceptable.  If you want JST
> to be recognized as a zic timezone, go lobby the upstream maintainers
> for it.

Ok. Until it gets fixed, I will make a Japanese document somewhere
else (maybe on my web pages or my books) to avoid the confusion for
PostgreSQL users in Japan.
--
SRA OSS, Inc. Japan
Tatsuo Ishii


Re: Allowed timezone values

From
Tatsuo Ishii
Date:
> Also, JST doesn't work anymore, but JST9 does.  JST has no daylight
> savings time values, so it is a valid timezone setting, while EST is
> not:
> 
>     test=> set timezone = 'JST';
>     ERROR:  unrecognized time zone name: "JST"
> 
>     test=> set timezone = 'JST9';
>     SET

Note that JST and JST9 are completely different timezone. I'm not
sure this is correct or not...

test=# select '2005-10-14 18:00' at time zone 'JST';     timezone       
---------------------2005-10-15 08:00:00
(1 row)

test=# select '2005-10-14 18:00' at time zone 'JST9';     timezone       
---------------------2005-10-14 14:00:00
(1 row)
--
SRA OSS, Inc. Japan
Tatsuo Ishii


Re: Allowed timezone values

From
Bruce Momjian
Date:
Tatsuo Ishii wrote:
> > Also, JST doesn't work anymore, but JST9 does.  JST has no daylight
> > savings time values, so it is a valid timezone setting, while EST is
> > not:
> > 
> >     test=> set timezone = 'JST';
> >     ERROR:  unrecognized time zone name: "JST"
> > 
> >     test=> set timezone = 'JST9';
> >     SET
> 
> Note that JST and JST9 are completely different timezone. I'm not
> sure this is correct or not...
> 
> test=# select '2005-10-14 18:00' at time zone 'JST';
>       timezone       
> ---------------------
>  2005-10-15 08:00:00
> (1 row)
> 
> test=# select '2005-10-14 18:00' at time zone 'JST9';
>       timezone       
> ---------------------
>  2005-10-14 14:00:00
> (1 row)

Oops, yea.  It should be JST-9:
test=> select '2005-10-14 18:00' at time zone 'JST-9';      timezone--------------------- 2005-10-15 07:00:00(1
row)test=>select '2005-10-14 18:00' at time zone 'JST';      timezone--------------------- 2005-10-15 07:00:00(1 row)
 

--  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