473,237 Members | 1,184 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,237 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 4718
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: perplexed | last post by:
How do you convert a user inputted date to a unix timestamp before insterting it into your database? I have a form, with a textfield for a date that the user inputs in the format mm-dd-yyyy and...
7
by: Jim Davis | last post by:
I'm (still) working on an ISO 8601 date parser. I want to convert at least the formats described here: http://www.w3.org/TR/NOTE-datetime Well.. I've got most of it working (via RegEx's) good...
1
by: cnliou | last post by:
Hi! If I correctly understand v7.4 manual, value, say, '2003-11-26 12:00' in TIMESTAMP WITHOUT TIMEZONE column should output '2003-11-26 19:00' for "+08:00" timezone. The following test...
1
by: Chris M. Gamble | last post by:
I recently upgraded an application from postgresql 7.4.2 to 7.4.5, and now I am having timezone nightmares. I checked on the old server, and found that in the postgresql.conf, the timezone was set...
13
by: Stuart Bishop | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi. I'm trying to determine the best way of saying 'The current time in UTC with no time zone information'. I'm currently using...
13
by: Bruno Wolff III | last post by:
Recently there has been some discussion about attaching a timezone to a timestamp and some other discussion about including a 'day' part in the interval type. These two features impact each other,...
0
by: Derek Fountain | last post by:
I'm using PHP4 and have got myself confused over timezones. I've put a datetime into my MySQL table as a GMT time. MySQL shows me the data as '2007-04-06 16:04:56'. I put that in there just after...
18
by: newbie | last post by:
Hi, I wonder if it's possible to detect a user (the browser's) timezone when he/she access my php file? I saw ppl suggesting using javascript to collect that information. But I wonder if it's...
5
by: Ivan Velev | last post by:
Hello, Minimal example below - it gives me different output if I comment / uncomment the extra time.mktime call - note that this call is not related in any way to main logic flow. When...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

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.