Oracle Date Timestamp with Timezone and Day Light Saving

-- Storage
DATE  					7 bytes
TIMESTAMP				7 or 11 bytes 
TIMESTAMP WITH TIME ZONE 		13 bytes 	(two more bytes for time zone information)
TIMESTAMP WITH LOCAL TIME ZONE 	        11 bytes 	(stores the timestamp without time zone information)

-- --------------------------------------------------------------------------------------
-- Get UTC (Coordinated Universal Time, formerly Greenwich Mean Time
-- The Day Light Saving is *NOT* considered

-- Tested on America/Chicago database, currently -5:00
-- January
select sys_extract_utc(to_timestamp('Jan 04 2012, 12:00 AM', 'Mon DD YYYY, HH:MI AM')) from dual;
04-JAN-12 05.00.00.000000000 AM

-- July
select sys_extract_utc(to_timestamp('Jul 04 2012, 12:00 AM', 'Mon DD YYYY, HH:MI AM')) from dual;
04-JAN-12 05.00.00.000000000 AM

-- --------------------------------------------------------------------------------------
-- Convert time between two time zones (with Day Light Saving considered)
-- Note: America/Phoenix does *not* observe the Day Light Saving.

-- January - NOT in Day Light Saving
SELECT FROM_TZ(to_timestamp('Jan 04 2012, 12:00 AM', 'Mon DD YYYY, HH:MI AM'), 'America/Phoenix') 
AT TIME ZONE 'America/Chicago'  FROM DUAL;
04-JAN-12 01.00.00.000000 AM AMERICA/CHICAGO

-- July - in Day Light Saving
SELECT FROM_TZ(to_timestamp('Jul 04 2012, 12:00 AM', 'Mon DD YYYY, HH:MI AM'), 'America/Phoenix') 
AT TIME ZONE 'America/Chicago'  FROM DUAL;
04-JUL-12 02.00.00.000000000 AM AMERICA/CHICAGO


-- Using CAST function to convert string to timestamp (instead of using to_timestamp function)
-- Yielding the same result

SELECT FROM_TZ(CAST(TO_DATE('Jan 04 2012, 12:00 AM', 'Mon DD YYYY, HH:MI AM') AS TIMESTAMP), 'America/Phoenix') 
AT TIME ZONE 'America/Chicago'  FROM DUAL;
04-JAN-12 01.00.00.000000 AM AMERICA/CHICAGO

SELECT FROM_TZ(CAST(TO_DATE('Jul 04 2012, 12:00 AM', 'Mon DD YYYY, HH:MI AM') AS TIMESTAMP), 'America/Phoenix') 
AT TIME ZONE 'America/Chicago'  FROM DUAL;
04-JUL-12 02.00.00.000000 AM AMERICA/CHICAGO

-- --------------------------------------------------------------------------------------
-- Convert to UTC for Jan and Jul (same as using sys_extract_utc function)
SELECT FROM_TZ(to_timestamp('Jan 04 2012, 12:00 AM', 'Mon DD YYYY, HH:MI AM'), 'America/Chicago') 
AT TIME ZONE 'UTC'  FROM DUAL;
04-JAN-12 06.00.00.000000000 AM UTC

SELECT FROM_TZ(to_timestamp('Jul 04 2012, 12:00 AM', 'Mon DD YYYY, HH:MI AM'), 'America/Chicago') 
AT TIME ZONE 'UTC'  FROM DUAL;
04-JUL-12 05.00.00.000000000 AM UTC

SELECT FROM_TZ(to_timestamp('Jan 04 2012, 12:00 AM', 'Mon DD YYYY, HH:MI AM'), 'America/Phoenix') 
AT TIME ZONE 'UTC'  FROM DUAL;
04-JAN-12 07.00.00.000000000 AM UTC

SELECT FROM_TZ(to_timestamp('Jul 04 2012, 12:00 AM', 'Mon DD YYYY, HH:MI AM'), 'America/Phoenix') 
AT TIME ZONE 'UTC'  FROM DUAL;
04-JUL-12 07.00.00.000000000 AM UTC

-- Using sys_extract_utc
select sys_extract_utc(
	FROM_TZ(CAST(TO_DATE('Jan 04 2012, 12:00 AM', 'Mon DD YYYY, HH:MI AM') AS TIMESTAMP), 'America/Chicago')
	) from dual;
04-JAN-12 06.00.00.000000 AM
	
select sys_extract_utc(
	FROM_TZ(CAST(TO_DATE('Jul 04 2012, 12:00 AM', 'Mon DD YYYY, HH:MI AM') AS TIMESTAMP), 'America/Chicago')
	) from dual;
04-JUL-12 05.00.00.000000 AM
	
select sys_extract_utc(
	FROM_TZ(CAST(TO_DATE('Jan 04 2012, 12:00 AM', 'Mon DD YYYY, HH:MI AM') AS TIMESTAMP), 'America/Phoenix')
	) from dual;
04-JAN-12 07.00.00.000000 AM

select sys_extract_utc(
	FROM_TZ(CAST(TO_DATE('Jul 04 2012, 12:00 AM', 'Mon DD YYYY, HH:MI AM') AS TIMESTAMP), 'America/Phoenix')
	) from dual;
04-JUL-12 07.00.00.000000 AM

-- --------------------------------------------------------------------------------------
-- Convert to DATE datatype using CAST
alter session set NLS_DATE_FORMAT='MM/DD/YYYY HH:MI AM';
select cast(
			sys_extract_utc(FROM_TZ(CAST(TO_DATE('Jan 04 2012, 12:00 AM', 'Mon DD YYYY, HH:MI AM') AS TIMESTAMP), 'America/Chicago')) 
			as DATE)			
from dual;
01/04/2012 06:00 AM