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