In search of nonexistent time

On Friday, in an application installed on a test site, a bug was found related to a library conflict, which for some reason did not appear at the development stage and which stopped the process being processed. We quickly prepared the patch and passed the updated distribution to the implementation team. In turn, the implementation team created a request to the administration team to install the distribution kit on the test site. On the weekend, the shift on duty reached this application and updated the application. On Monday morning it was discovered that the process was stalled again.

We analyzed the application server logs and found many lines of the form:

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

Google on the error code prompted me http://stackoverflow.com/questions/22305466/oracle-date-compare-broken-because-of-dst The

culprit was found very quickly - in the application for spring integration a task handler of the following form was implemented :


Actually, the culprit

SELECT ID, UPDATE_TIME
FROM TASK 
WHERE 
UPDATE_TIME IS NULL OR SYSTIMESTAMP>(UPDATE_TIME+ INTERVAL '3500' SECOND)

The request was successfully fulfilled on the basis of developers, but fell on a test basis. The search for solutions was started.
First, on the advice of the article, option No. 1 was tried

SELECT ID, UPDATE_TIME 
FROM  TASK 
WHERE 
UPDATE_TIME IS NULL OR SYSTIMESTAMP>CAST(UPDATE_TIME+ INTERVAL '3500' SECOND AS TIMESTAMP WITH TIME ZONE)

The request was successfully processed on the basis of developers, and on a test base from the administrator console. A distribution kit with a fix was prepared and quickly installed. Which in fact did not fix anything. It became clear that the problem depends on the session connection settings.
Tables data were requested and received from the test site. And two lines immediately aroused the suspicion of UPDATE_TIME in them falling on March 29 at 1:30 in the night - the last Sunday of March. After calculating

UPDATE_TIME+ INTERVAL '3500' SECOND

just falls into the interval between 2:00 and 3:00 a.m. non-existent time for a time zone using DST.
To check suspicions, similar data were entered into the development database - the Request continued to work without failures.
I try with

alter session set time_zone =’europe/warsaw’

And I hit the target - I managed to repeat the mistake at the development site. This could be stopped by requesting the installation of the appropriate time zone updates to the database (for details on Oracle updates at the end of the article). But I wondered if this behavior could be fixed by rewriting the SQL query.

I'm trying to transfer entropy from one part of the expression to another option No. 2

SELECT ID, UPDATE_TIME from TABLE1 WHERE UPDATE_TIME IS NULL OR ( SYSTIMESTAMP - INTERVAL '3500' SECOND )>  UPDATE_TIME

Everything is OK, but we make the assumption that SYSTIMESTAMP can still take the value from the “non-existent time” and, accordingly, in the year one hour is possible when the application does not work.

We come to option number 3

SELECT ID, UPDATE_TIME 
FROM TASK 
WHERE 
UPDATE_TIME IS NULL OR ( SYSTIMESTAMP -  UPDATE_TIME ) > INTERVAL '3500' SECOND

Everything seems to be fine, but what if you insert a record into the table with a time between 2:00 and 3:00 at night. I try on March 29 at 2:30 am - requests stop working.

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

There is no reception against scrap - option No. 4

SELECT ID,  UPDATE_TIME 
FROM TASK 
WHERE 
UPDATE_TIME IS NULL OR (to_timestamp_tz(to_char(SYSTIMESTAMP,'rrrr-mm-dd hh24:mi:ss')||' '||'UTC','rrrr-mm-dd hh24:mi:ss tzr') -  to_timestamp_tz(to_char(UPDATE_TIME,'rrrr-mm-dd hh24:mi:ss')||' '||'UTC','rrrr-mm-dd hh24:mi:ss tzr') ) > INTERVAL '3500' SECOND 

Everything works - but I want to find a solution to it easier. I re-read the article on stackoverflow and the Oracle documentation before enlightenment:

1. The problem is that UPDATE_TIME, unlike SYSTIMESTAMP, is declared without a time zone, which leads to implicit type conversion in the original query and queries No. 2 and 3. Request for verification
   

   SELECT ID, CAST(UPDATE_TIME+ INTERVAL '3500' SECOND AS TIMESTAMP WITH TIME ZONE) FROM  TASK 
   

   
   
2. If you use LOCALTIMESTAMP instead of SYSTIMESTAMP, then everything will work
   

   SELECT ID, UPDATE_TIME
   FROM TASK 
   WHERE 
   UPDATE_TIME IS NULL OR LOCALTIMESTAMP >(UPDATE_TIME+ INTERVAL '3500' SECOND)
   

   
   
3. You can also change the type of the UPDATE_TIME field to TIMESTAMP with time zone and do not forget to put time zone updates on Oracle
4. You can make the current date as a parameter and pass from the application - everything will work.
5. If for some reason you need a timestamp without a time zone in combination with SYSTIMESTAMP, then you need to cast not to the type returned by SYSTIMESTAMP but to the type of the field UPDATE_TIME
   

   SELECT ID, UPDATE_TIME
   FROM TASK 
   WHERE 
   UPDATE_TIME IS NULL OR CAST(SYSTIMESTAMP AS TIMESTAMP) >(UPDATE_TIME+ INTERVAL '3500' SECOND)
   

   
   

Z.Y. As promised above, the description of the installation of database time zone updates can be read in the article "Switching to Oracle Database Winter Time in 2014" .