Re: Simplifying timezone support

On Wed, Feb 19, 2003 at 10:35:58PM -0500, Tom Lane wrote:
<snip Tom discussion backend internal tracking of timezone>
> Any objections?

Not to your suggestion per se, but looking at the bug report raises a
question about pgsql's time zone parsers. It appears there's at least
two, since SET TIME ZONE accepts strings like 'US/Eastern', while general
timestamp parsing doesn't:

test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 CST';
timestamptz
------------------------------
2003-02-18 09:36:06.00933-06
(1 row)

test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST';
timestamptz
------------------------------
2003-02-18 08:36:06.00933-06
(1 row)

test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 US/Eastern';
ERROR: Bad timestamp external representation '2003/02/18 09:36:06.00933 US/Eastern'

Further testing says it's even worse that that:

SET TIME ZONE will silently accept any string at all, and fall back to
providing GMT when a timestamptz is requested. This includes the TLA
TZ abbreviations that the constant parsing code understands, like CST
and EST.

test=# set TIME ZONE 'CST';
SET
test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST';
timestamptz
------------------------------
2003-02-18 14:36:06.00933+00
(1 row)

test=# set TIME ZONE 'FOOBAR';
SET
test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST';
timestamptz
------------------------------
2003-02-18 14:36:06.00933+00
(1 row)

Here's an especially fun one: with DATESTYLE set to 'Postgresql,US', whatever
string is handed to SET TIME ZONE comes out the other end, if it can't
be parsed:

test=# set TIME ZONE 'FOOBAR';
SET
test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST';
timestamptz
---------------------------------------
Tue Feb 18 14:36:06.00933 2003 FOOBAR
(1 row)

Leading to this erroneous pair:

test=# set TIME ZONE 'US/Central';
SET
test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST';
timestamptz
------------------------------------
Tue Feb 18 08:36:06.00933 2003 CST
(1 row)

test=# set TIME ZONE 'CST';
SET
test=# select TIMESTAMP WITH TIME ZONE '2003/02/18 09:36:06.00933 EST';
timestamptz
------------------------------------
Tue Feb 18 14:36:06.00933 2003 CST
(1 row)

test=#

Tom, since you're in (or near) that code right now, how painful would
it be to unify the time zone parsing? What's the correct behavior?
Certainly SET TIME ZONE should at leat NOTICE about invalide time zone
names?

Ross