Oracle Date Timestamp with Timezone and Day Light Saving
Jump to navigation
Jump to search
-- 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