jgirvin <james.girvin@xxxxxxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
