In search of a non-existent time

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

An analysis of the application server logs was discovered many lines of the form:

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

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

The culprit was found very quickly - a task handler of the following type was implemented in the spring integration application:
')

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 worked out on the basis of the developers, but fell on the test base. The search for solutions was started.
First, on the advice of the article was tested option number 1

 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 worked out on the basis of developers, and on a test base from the administrator console. A distributive distribution kit was prepared and promptly installed. Which in fact did not fix anything. It became clear that the problem depends on the session connection parameters.
Were requested and obtained data tables from the test site. And two lines immediately aroused suspicion of UPDATE_TIME in them on March 29, 1:30 am - the last Sunday of March. After calculation

 UPDATE_TIME+ INTERVAL '3500' SECOND 

just falls in the interval between 2:00 and 3:00 at night - the non-existent time for the time zone using DST.
To check the suspicions, similar data were entered into the development database - The request continued to work without fail.
I try with

 alter session set time_zone ='europe/warsaw' 

And I hit the target - I managed to repeat the mistake on the development site. This could have stopped by requesting the installation of the corresponding time zone updates on the database (detailed information on Oracle updates at the end of the article). But I wondered if it was possible to correct this behavior by rewriting the SQL query.

I try to transfer the entropy from one part of the expression to another option number 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 the SYSTIMESTAMP value can still be taken from “non-existent time” and, accordingly, one hour is possible in a year when the application is not working.

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 into the table an entry with a time between 2:00 and 3:00 in the morning. I try 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 number 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 easier. I reread the article on stackoverflow and the Oracle documentation before the onset of 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 2 and 3. The query 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 remember to install the time zone updates on Oracle
4. You can put the current date as a parameter and transfer it from the application - everything will work.
5. If for some reason you need a timestamp without a time zone in conjunction with SYSTIMESTAMP - then you need to bring not the type returned by SYSTIMESTAMP to the type of field UPDATE_TIME
   

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

   
   

ZY As promised above, a description of the installation of updates of time zones of the database can be read in the article “Switching to the Oracle database winter time in 2014” .