Thread: time zone?

Hi, it seems there is an inconsistency with time zone handling to me:

test=# select version();                          version                           
-------------------------------------------------------------PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC
2.95.3
(1 row)

test=# set time zone 'JST';
ERROR:  unrecognized time zone name: "JST"
test=# set time zone 'Japan';
SET
test=# select '2003-12-16 12:00:00 JST'::timestamp with time zone;     timestamptz       
------------------------2003-12-16 12:00:00+09
(1 row)

test=# select '2003-12-16 12:00:00 Japan'::timestamp with time zone;
ERROR:  invalid input syntax for type timestamp with time zone: "2003-12-16 12:00:00 Japan"

I know that this is because we need to use some OS's functionalities
to handle day light saving time etc. But this kind of inconsistency
makes users quite confusing. Any idea to improve this?
--
Tatsuo Ishii

Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> Hi, it seems there is an inconsistency with time zone handling to me:

Yeah, the set of time zone names accepted by SET TIME ZONE doesn't have
a whole lot to do with the set of time zone names you can specify as
part of a timestamptz input string.

This is partly an implementation issue and partly fundamental.

The implementation reason is that SET TIME ZONE just hands off its input
string to tzset(), and so the valid time zone names are whatever the
local C library will take; whereas time zone names accepted in
timestamptz input are those listed in the table in datetime.c.

The fundamental reason is that SET TIME ZONE's zones explicitly or
implicitly specify daylight-savings transition rules, whereas
timestamptz only wants to know about a particular offset from GMT.
For example, around here I can SET TIME ZONE to 'EST5EDT', which is
the traditional Unix notation for "EST (GMT-5) except during daylight
savings time, when it's GMT-4".  But EST5EDT isn't a meaningful spec in
timestamptz input --- I can say either EST or EDT, but not both.
On a Linux system I might instead spell the SET TIME ZONE value as
'US/Eastern', but I can't use that as a timestamptz input string.

We've talked about writing our own timezone library to avoid the
platform dependency of SET TIME ZONE names, but I don't see that
that would magically solve the discrepancy between
zones-with-DST-behavior and plain-old-GMT-offset zones.
        regards, tom lane