471,108 Members | 1,298 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

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

Nov 23 '05 #1
2 4603
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
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.

Similar topics

7 posts views Thread by Jim Davis | last post: by
1 post views Thread by cnliou | last post: by
1 post views Thread by Chris M. Gamble | last post: by
13 posts views Thread by Stuart Bishop | last post: by
13 posts views Thread by Bruno Wolff III | last post: by
reply views Thread by Derek Fountain | last post: by
18 posts views Thread by newbie | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.