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