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