Different results for TO_TIMESTAMP_TZ and CAST AS TIMESTAMP WITH LOCAL TIME ZONE in Oracle DB

B

Brian

Guest
I have an Oracle 11g database and I'm trying to experiment with how DST is handled with TIMESTAMP datatypes (especially ones created from incoming data that doesn't have a time zone included). I'm seeing a difference in behavior when using TO_TIMESTAMP_TZ() vs CAST(DATE as TIMESTAMP WITH LOCAL TIME ZONE) that I can't explain. I would expect that the resulting datatype is a zoned timestamp regardless of how it is created and any operation on it would be identical but it does not appear that this is the case.

Any idea why when using the CAST option the "extra" hour created by the Fall DST change isn't being taken into account in the INTERVAL math? I would expect both rows of the query below to be identical.

Query:

select to_timestamp_tz('11/1/2015 1:00 AM US/Pacific', 'MM/DD/YYYY HH:mi AM TZR') + interval '2' hour as DST_PLUS_2, -- this is a post DST jump time
to_timestamp_tz('11/1/2015 12:59 AM US/Pacific', 'MM/DD/YYYY HH:mi AM TZR') + interval '2' hour as PRE_DST_PLUS_2, -- this is a pre DST jump time
to_char(to_timestamp_tz('11/1/2015 1:00 AM US/Pacific', 'MM/DD/YYYY HH:mi AM TZR'), 'TZR') as TZR
from dual
union all
select cast(to_date('11/1/2015 1:00 AM', 'MM/DD/YYYY HH:mi AM') as timestamp with local time zone) + interval '2' hour as DST_PLUS_2,
cast(to_date('11/1/2015 12:59 AM', 'MM/DD/YYYY HH:mi AM') as timestamp with local time zone) + interval '2' hour as PRE_DST_PLUS_2,
to_char(cast(to_date('11/1/2015 1:00 AM', 'MM/DD/YYYY HH:mi AM') as timestamp with local time zone), 'TZR') as TZR
from dual;


Results:

DST_PLUS_2 PRE_DST_PLUS_2 TZR
01-NOV-15 03.00.00.000000000 AM US/PACIFIC 01-NOV-15 01.59.00.000000000 AM US/PACIFIC US/PACIFIC
01-NOV-15 03.00.00.000000000 AM US/PACIFIC 01-NOV-15 02.59.00.000000000 AM US/PACIFIC US/PACIFIC

Continue reading...
 
Top