Обсуждение: dst question
Hi,
Australia will come out of DST on the 4th April 2010 at 03:00:00 and
will be +9:30 from utc, currently we are +10:30 utc.
I have some plpgsql functions which have variables of type timestamp
defined with time zone. These variables are used in various ways,
sometimes they will receive a text representation of a timestamp and are
consequently populated with var := to_timestamp( text, 'format'), other
times they are populated with existing timestamps from other tables
columns or from the result of now().
The newly populated timestamp variable is then used to populate another
tables 'timestamp with time zone' column.
I have noticed that when we are within the hour as to when DST will
reset ( ie 4th April 2010 between 02:00:00 and 03:00:00 ) the
resulting timestamp put into our final table is already set to +9:30 utc
even though we have not reached the actual time when dst changes. This
happens when a string is converted using to_timestamp.
Below is an example showing the results of a now() and to_timestamp(
to_char(now(),'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') prior to
entering the hour before the dst switch and within the hour of the dst
switch.
This is prior to entering the hour before dst changeover. both
timestamps correctly show the +10:30 offset.
# select now(), to_timestamp( to_char(now(),'yyyy-mm-dd
hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss');
now | to_timestamp
----------------------------------+---------------------------
2010-04-04 01:53:32.471086+10:30 | 2010-04-04 01:53:32+10:30
(1 row)
This is 4 seconds into the last hour prior to dst changeover, now the
to_timestamp result is showing +09:30 as the offset and now() correctly
shows +10:30
# select now(), to_timestamp( to_char(now(),'yyyy-mm-dd
hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss');
now | to_timestamp
----------------------------------+---------------------------
2010-04-04 02:00:04.841797+10:30 | 2010-04-04 02:00:04+09:30
(1 row)
Can someone explain as to why the output from the to_timestamp shows the
offset at +09:30 when within the hour of the dst changeover and is this
expected....
cheers.
--
James.
__________ Information from ESET NOD32 Antivirus, version of virus signature database 4933 (20100310) __________
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
jgirvin <james.girvin@oasissystems.com.au> writes:
> Australia will come out of DST on the 4th April 2010 at 03:00:00 and
> will be +9:30 from utc, currently we are +10:30 utc.
OK, so this is a "fall back" transition for you guys, right?
> This is 4 seconds into the last hour prior to dst changeover, now the
> to_timestamp result is showing +09:30 as the offset and now() correctly
> shows +10:30
> # select now(), to_timestamp( to_char(now(),'yyyy-mm-dd
> hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss');
> now | to_timestamp
> ----------------------------------+---------------------------
> 2010-04-04 02:00:04.841797+10:30 | 2010-04-04 02:00:04+09:30
> (1 row)
> Can someone explain as to why the output from the to_timestamp shows the
> offset at +09:30 when within the hour of the dst changeover and is this
> expected....
The problem is that the output of to_char() is ambiguous, since you
didn't include the timezone in the format spec. Times between 02:00 and
03:00 occur twice on that date, and there's no way to know which time
"02:00:04" refers to. The assumption that to_timestamp uses (along with
our other datetime input code) is that an ambiguous time should be
resolved as standard time, ie, the second occurrence of "02:00:04".
regards, tom lane
Hi Tom, thanks for the response. Tom Lane wrote: > jgirvin <james.girvin@oasissystems.com.au> writes: > >> Australia will come out of DST on the 4th April 2010 at 03:00:00 and >> will be +9:30 from utc, currently we are +10:30 utc. >> > > OK, so this is a "fall back" transition for you guys, right? > Yes this is correct Adelaide, South Aust will be coming out of (Australian) CDT back into (Australian) CST. > >> This is 4 seconds into the last hour prior to dst changeover, now the >> to_timestamp result is showing +09:30 as the offset and now() correctly >> shows +10:30 >> # select now(), to_timestamp( to_char(now(),'yyyy-mm-dd >> hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'); >> > > >> now | to_timestamp >> ----------------------------------+--------------------------- >> 2010-04-04 02:00:04.841797+10:30 | 2010-04-04 02:00:04+09:30 >> (1 row) >> > > >> Can someone explain as to why the output from the to_timestamp shows the >> offset at +09:30 when within the hour of the dst changeover and is this >> expected.... >> > > The problem is that the output of to_char() is ambiguous, since you > didn't include the timezone in the format spec. Times between 02:00 and > 03:00 occur twice on that date, and there's no way to know which time > "02:00:04" refers to. The assumption that to_timestamp uses (along with > our other datetime input code) is that an ambiguous time should be > resolved as standard time, ie, the second occurrence of "02:00:04". > Okay, that makes sense. > regards, tom lane > > __________ Information from ESET NOD32 Antivirus, version of virus signature database 4933 (20100310) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > > > > > -- James Girvin Oasis Systems Pty Limited Level 1, 206 Greenhill Road Eastwood, South Australia 5063 Voice: +61 8 8372 9057 Fax: +61 8 8372 9051 http://www.oasissystems.com.au mailto:james.girvin@oasissystems.com.au This email is subject to the following policy: http://www.oasissystems.com.au/email_policy.html __________ Information from ESET NOD32 Antivirus, version of virus signature database 4937 (20100311) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com