05 September 2007

Convert UTC Time To Local Time In Oracle

Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.5.0
Connected as sysadm

SQL> SELECT SYSTIMESTAMP,
  2         SYS_EXTRACT_UTC(SYSTIMESTAMP) UTC_DATE,
  3         DBTIMEZONE,
  4         SYSTIMESTAMP - SYS_EXTRACT_UTC(SYSTIMESTAMP) DIFF
  5    FROM dual;

SYSTIMESTAMP                                      UTC_DATE                                          DBTIMEZONE DIFF
------------------------------------------------- ------------------------------------------------- ---------- -------------------
05/09/2007 11:39:27,992823 +03:00                 05/09/2007 08:39:27,992823                        +03:00     +000000000 03:00:00

SQL> SELECT * FROM v$timezone_names where tzname like 'Asia/Istanbul' and rownum = 1;
TZNAME                                                           TZABBREV
---------------------------------------------------------------- ----------------------------------------------------------------
Asia/Istanbul                                                    LMT

SQL> SELECT utc_date,
  2         FROM_TZ(CAST( utc_date AS TIMESTAMP), 'Greenwich') AT TIME ZONE 'Asia/Istanbul' as local_date,
  3         TO_CHAR(  FROM_TZ(CAST( utc_date AS TIMESTAMP), 'Greenwich') AT TIME ZONE 'Asia/Istanbul', 'MM.DD.RRRR HH24:MI:SS' ) local_date
  4   FROM ( SELECT TO_DATE('2007-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS') utc_date FROM dual
  5           UNION
  6           SELECT TO_DATE('2007-08-01 11:00:00', 'YYYY-MM-DD HH:MI:SS' ) FROM dual
  7         );

UTC_DATE    LOCAL_DATE                                        LOCAL_DATE
----------- ------------------------------------------------- -------------------
01.08.2007  01/08/2007 14:00:00,000000 ASIA/ISTANBUL          08.01.2007 14:00:00
01.12.2007  01/12/2007 13:00:00,000000 ASIA/ISTANBUL          12.01.2007 13:00:00

SQL>