Обсуждение: timezone abbreviation in timestamp string input

When a timestamp string input contains a timezone abbreviation (CDT,
PST, etc), which timezone offset is used? The input date's or today
date's? The result on my computer suggests the latter.

# create table ts (ts timestamptz);
# insert into ts values ('2004-10-17 00:00:00 CDT'); -- UTC-5
# insert into ts values ('2004-11-17 00:00:00 CDT'); -- UTC-6
# select ts at time zone 'utc' from ts;
       timezone
---------------------
  2004-10-17 05:00:00
  2004-11-17 05:00:00
(2 rows)

If this is true, then perhaps forbid timezone abbreviation in input
string, or emit warning about this?

--
dave

On Sun, Oct 17, 2004 at 04:45:45PM +0700, David Garamond wrote:
> When a timestamp string input contains a timezone abbreviation (CDT,
> PST, etc), which timezone offset is used? The input date's or today
> date's? The result on my computer suggests the latter.
>
> # create table ts (ts timestamptz);
> # insert into ts values ('2004-10-17 00:00:00 CDT'); -- UTC-5
> # insert into ts values ('2004-11-17 00:00:00 CDT'); -- UTC-6
> # select ts at time zone 'utc' from ts;
>       timezone
> ---------------------
>  2004-10-17 05:00:00
>  2004-11-17 05:00:00
> (2 rows)

The input strings specifically say that the timezone is CDT (UTC-5),
so apparently that's the offset the database uses, regardless of
date.  If you set the session's timezone to CST6CDT and omit the
timezone specification, then the database should use the offset
that would be in effect on that date:

SET TimeZone TO 'CST6CDT';
INSERT INTO TS VALUES ('2004-10-17 00:00:00');
INSERT INTO TS VALUES ('2004-11-17 00:00:00');
SELECT ts AT TIME ZONE 'UTC' FROM ts;
      timezone
---------------------
 2004-10-17 05:00:00
 2004-11-17 06:00:00

> If this is true, then perhaps forbid timezone abbreviation in input
> string, or emit warning about this?

Maybe a warning that the specified timezone wouldn't be in effect
on the given date?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr <mike@fuhr.org> writes:
> Maybe a warning that the specified timezone wouldn't be in effect
> on the given date?

No thanks.  It is not wrong for example to refer to EST all year round.

            regards, tom lane