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/