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>