By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,086 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

timezone abbreviation in timestamp string input

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #2

P: n/a
Michael Fuhr <mi**@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.