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