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
