Skip to Main Content
  • Questions
  • New_time function is failed to convert dst changes for date column

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rama.

Asked: February 23, 2019 - 5:28 am UTC

Last updated: May 30, 2019 - 4:00 am UTC

Version: 12C

Viewed 1000+ times

You Asked

Hi ,

I have 2 table columns with date as datatype. 1 stores in central standard time other in central local time. Everyday a batch runs to populate these values to another oracle db. target db column is in eastern local. when I use new_time function to convert central standard and central local to eastern local, dst changes are not reflected.


new_time is just adding 2 hours to cst time irrspective of dst changes as shown below.


select new_time(to_date('01-JAN-2018 22:00:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') ,
new_time(to_date('01-JUN-2018 22:00:00','DD-MON-YYYY HH24:MI:SS'),'CST','EDT') from dual;



Help me to convert central standard and central local to eastern local.

with LiveSQL Test Case:

and Connor said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library