3 Replies Latest reply: Oct 25, 2010 7:34 AM by Sergiusz Wolicki-Oracle RSS

    DST Time with from_tz, to_timestamp_tz

    132145
      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