Check the following MOS note:
DST Has No Effect on NEW_TIME function (Doc ID 419404.1)
From that note:
NEW_TIME limitations - no automatic DST changes
The most important thing to realize when using the new_time function is that all the above mentioned possible time zones are completely static. For example EST always means "GMT-5", EDT always means "GMT-4", PST always means "GMT-8", etc. etc. Therefore any program using the new_time function must itself have knowledge about the start- and end-dates of DST, in order to do a correct conversion to the correct time for the time of year. The new_time function never has had, and never will have, automatic DST switchover between time zones. Essentially the new_time function is simply a function that subtracts or adds a number of hours from a DATE, where the names that can be used are simply a alias for a fixed number of hours.So you can handle it explicitly, eg
SQL> alter session set nls_timestamp_tz_format = 'DD-MON-YYYY HH24:MI:SS TZR';
Session altered.
SQL> select to_timestamp_tz('05/05/2006 14:00:00 GMT', 'MM/DD/YYYY HH24:MI:SS TZR') at time zone 'US/Eastern' from dual;
TO_TIMESTAMP_TZ('05/05/200614:00:00GMT','MM/DD/YYYYHH24:MI:SSTZR')ATTIMEZON
---------------------------------------------------------------------------
05-MAY-2006 10:00:00 US/EASTERN
SQL> select to_timestamp_tz('01/01/2006 14:00:00 GMT', 'MM/DD/YYYY HH24:MI:SS TZR') at time zone 'US/Eastern' from dual;
TO_TIMESTAMP_TZ('01/01/200614:00:00GMT','MM/DD/YYYYHH24:MI:SSTZR')ATTIMEZON
---------------------------------------------------------------------------
01-JAN-2006 09:00:00 US/EASTERN
or you could write a wrapper to mimic new_time but with dst handling
create or replace function new_time_dst(in_date date, tz1 varchar2, tz2 varchar2) return date deterministic as
begin
return to_date(
to_char(
to_timestamp_tz(
to_char(in_date,'YYYYMMDDHH24:MI:SS')||' '||trim(tz1)
,'YYYYMMDDHH24:MI:SS TZR') at time zone tz2
,'YYYYMMDDHH24:MI:SS')
,'YYYYMMDDHH24:MI:SS');
end;
/
SQL> SELECT NEW_TIME_DST(TO_DATE('05/05/2006 14:00:00', 'MM/DD/YYYY HH24:MI:SS'),'GMT','US/EASTERN') from dual;
NEW_TIME_DST(TO_DATE
--------------------
05-MAY-2006 10:00:00
SQL> SELECT NEW_TIME_DST(TO_DATE('01/01/2006 14:00:00', 'MM/DD/YYYY HH24:MI:SS'),'GMT','US/EASTERN') from dual;
NEW_TIME_DST(TO_DATE
--------------------
01-JAN-2006 09:00:00
Addenda: Thanks to Roderick Manalac for pointing this out. You need to remember that the determinism in this case will only be valid as long as timezone definitions for your particular regions do not change. In some parts of the world, they change whether they *have* daylight saving, or when it starts and stops. You'd need to take care here.