ORA-01878: specified field not found in datetime or interval

In the morning of this Monday, right after Daylight Saving Time began on Sunday March 14th, I got asked by a colleague — he has a PL/SQL procedure which has been been working fine. But on Sunday morning, when DST started at 2am, the procedure generated the following error which had lasted for one hour:

ORA-01878: specified field not found in datetime or interval

I looked at the issue and it’s quite interesting. In the procedure there is a deletion from a table which has a timestamp with local time zone column; and the deletion has a criteria that only deletes rows older than an hour. So he compares that timestamp column with (sysdate-1/24). It is something like below:

CREATED < (sysdate - 1/24)

My first thought was maybe systimestamp (TIMESTAMP WITH TIME ZONE type) should have been used instead of sysdate (DATE type) as below:

CREATED < cast (SYSTIMESTAMP- interval '1' hour As timestamp with time zone)

After some reading I realized it’s not that simple, basically with the logic of the procedure — delete rows old than an hour, the error is inevitable and he needs either to write the execption handling to trap this non-predefined Oracle server error: ORA-01878 or to schedule his procedure to bypass this one hour period.

The problem is “one hour missing” when DST starts at 2am — the time moves forward to 3am. It won’t happen when DST ends — the time moves backward 3am to 2am.

Here are some examples to show the problem:

SQL>  select to_timestamp_tz('20210314 02:00:00 America/New_York','YYYYMMDD HH24:MI:SS TZR') from dual;
 select to_timestamp_tz('20210314 02:00:00 America/New_York','YYYYMMDD HH24:MI:SS TZR') from dual
                        *
ERROR at line 1:
ORA-01878: specified field not found in datetime or interval

SQL> select to_timestamp_tz('20210314 02:30:00 America/New_York','YYYYMMDD HH24:MI:SS TZR') from dual;
select to_timestamp_tz('20210314 02:30:00 America/New_York','YYYYMMDD HH24:MI:SS TZR') from dual
                       *
ERROR at line 1:
ORA-01878: specified field not found in datetime or interval

SQL> select to_timestamp_tz('20210314 02:59:59 America/New_York','YYYYMMDD HH24:MI:SS TZR') from dual;
select to_timestamp_tz('20210314 02:59:59 America/New_York','YYYYMMDD HH24:MI:SS TZR') from dual
                       *
ERROR at line 1:
ORA-01878: specified field not found in datetime or interval

SQL> select to_timestamp_tz('20210314 03:00:00 America/New_York','YYYYMMDD HH24:MI:SS TZR') from dual;

TO_TIMESTAMP_TZ('2021031403:00:00AMERICA/NEW_YORK','YYYYMMDDHH24:MI:SSTZR')
---------------------------------------------------------------------------
14-MAR-21 03.00.00.000000000 AM AMERICA/NEW_YORK

As you can see from the above query results, any attempts to find data between 2am~3am with timezone information will trigger the error.

As a comparison, without timezone info, query between 2am~3am works.

SQL> select to_timestamp('20210314 02:05:59','YYYYMMDD HH24:MI:SS') from dual;

TO_TIMESTAMP('2021031402:05:59','YYYYMMDDHH24:MI:SS')
---------------------------------------------------------------------------
14-MAR-21 02.05.59.000000000 AM

References: