Difference between revisions of "Oracle Date Timestamp with Timezone and Day Light Saving"
Jump to navigation
Jump to search
Line 1: | Line 1: | ||
<pre> | <pre> | ||
-- Storage | -- Storage | ||
− | DATE | + | DATE 7 bytes |
− | TIMESTAMP | + | TIMESTAMP 7 or 11 bytes |
− | TIMESTAMP WITH TIME ZONE 13 bytes | + | TIMESTAMP WITH TIME ZONE 13 bytes (two more bytes for time zone information) |
− | TIMESTAMP WITH LOCAL TIME ZONE | + | TIMESTAMP WITH LOCAL TIME ZONE 11 bytes (stores the timestamp without time zone information) |
-- -------------------------------------------------------------------------------------- | -- -------------------------------------------------------------------------------------- |
Revision as of 16:31, 5 September 2012
-- 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