This discussion is archived
3 Replies Latest reply: Oct 25, 2010 5:34 AM by Sergiusz Wolicki (Oracle) RSS

DST Time with from_tz, to_timestamp_tz

132145 Newbie
Currently Being Moderated
I am running this query to get UTC time from the local time zone Australia/Melbourne. But getting wrong output.Day light changes were ended from 02/oct/2010 in Australia. But this looks strange error. Any suggestions or help?


30/0ct/2010 -- results

select to_timestamp_tz('2010-10-30 14:00:00 Australia/Melbourne','yyyy-mm-dd hh24:mi:ss tzr') at time zone '00:00' from dual
SQL> /

TO_TIMESTAMP_TZ('2010-10-3014:00:00AUSTRALIA/MELBOURNE','YYYY-MM-DDHH24:MI:
---------------------------------------------------------------------------
30-OCT-10 04.00.00.000000000 AM +00:00

31-oct-2010-- results

select to_timestamp_tz('2010-10-31 14:00:00 Australia/Melbourne', 'yyyy-mm-dd hh24:mi:ss tzr') at time zone '00:00' from dual
SQL> /

TO_TIMESTAMP_TZ('2010-10-3114:00:00AUSTRALIA/MELBOURNE','YYYY-MM-DDHH24:MI:
---------------------------------------------------------------------------
31-OCT-10 03.00.00.000000000 AM +00:00


02/oct-2010 -results
select to_timestamp_tz('2010-10-02 14:00:00 Australia/Melbourne', 'yyyy-mm-dd hh24:mi:ss tzr') at time zone '00:00' from dual
SQL> /

TO_TIMESTAMP_TZ('2010-10-0214:00:00AUSTRALIA/MELBOURNE','YYYY-MM-DDHH24:MI:
---------------------------------------------------------------------------
02-OCT-10 04.00.00.000000000 AM +00:00

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points