SELECT dbtimezone, SESSIONTIMEZONE, tz_offset(SESSIONTIMEZONE) timez_offset, CURRENT_DATE, current_timestamp, localtimestamp, to_timestamp('20110101 09:05:00.000', 'YYYYMMDD HH24:MI:SS.FF') time_stamp_, TO_TIMESTAMP_TZ('2011-07-01 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM') To_timestamp_tz, FROM_tz(timestamp '2011-09-01 08:00:00','3:00') from_tz_, sys_extract_utc(systimestamp) utc_time FROM DUAL; => Above Query Output SELECT TO_CHAR(sysdate,'mm/dd/yyyy hh24:mi:ss') current_time, FROM_TZ(TO_TIMESTAMP(TO_CHAR(sysdate,'mm/dd/yyyy hh24:mi:ss'), 'mm/dd/yyyy hh24:mi:ss'),'UTC') AT TIME ZONE 'America/New_York' utc_2_current FROM DUAL; => Above Query Output --Converting UTC (specified as GMT) to EST Time. This only changes the time --setting in DATE data type. The DATE data type does not have the timezone --setting and NEW_TIME(date, From, to) function does efficient time shifting SELECT TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss') date_utc, TO_CHAR(NEW_TIME(SYSDATE, 'GMT', 'EST'),'mm/dd/yyyy hh24:mi:ss') date_utc_2_est FROM DUAL; --Output of Above Query Shown Below DATE_UTC DATE_UTC_2_EST ------------------- ------------------- 01/05/2014 19:06:20 01/05/2014 14:06:20 --Time Zone Name List SELECT * FROM v$timezone_names; --Converting DATE data to UTC (to timestamp) SELECT TO_CHAR(SYSDATE,'mm/dd/yyyy hh24:mi:ss') any_sysdate, TO_CHAR(systimestamp,'TZR') server_tz_offset, FROM_TZ(CAST(SYSDATE AS TIMESTAMP),'EST') at time zone 'UTC' est_2_utc, FROM_TZ(CAST(SYSDATE AS TIMESTAMP),'CST') at time zone 'UTC' cst_2_utc FROM DUAL; TimeZone => Listing using UTL_I18N
24108