We've been debugging an issue with a SQL query executed from an app server running Java via Hibernate. The error:
[3/10/14 10:52:07:143 EDT] 0000a984 JDBCException W org.hibernate.util.JDBCExceptionReporter logExceptions SQL Error: 1878, SQLState: 22008
[3/10/14 10:52:07:144 EDT] 0000a984 JDBCException E org.hibernate.util.JDBCExceptionReporter logExceptions ORA-01878: specified field not found in datetime or interval
We've been able to narrow this down to the simple SQL below.
select *
from MY_TABLE T
where T.MY_TIMESTAMP >= (CURRENT_TIMESTAMP - interval '1' hour );
When we run this in the same database, we get the error:
ORA-01878: specified field not found in datetime or interval
01878. 00000 - "specified field not found in datetime or interval"
*Cause: The specified field was not found in the datetime or interval.
*Action: Make sure that the specified field is in the datetime or interval.
The MY_TIMESTAMP column is defined as TIMESTAMP(6).
FWIW, if we change the comparison in the SQL above from >= to <=, the query works.
We assume this has something to do with the time change (we're in America/New_York) but we're having problems trying to figure out where to go from here with our debugging.
Also, we've seen this problem with a similar query that's running through MyBatis and the error looks like:
### Error querying database. Cause: java.sql.SQLException: ORA-01878: specified field not found in datetime or interval
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### Cause: java.sql.SQLException: ORA-01878: specified field not found in datetime or interval
UPDATE: A teammate on Windows changed her Windows Date and Time settings by un-checking "Automatically adjust clock for Daylight Saving Time" and then opened a new SQLDeveloper instance. The second instance is able to run the query without any issue but the first (with the old DST setting) still fails.
Thanks to kordirko for the extremely detailed write up. I think in the future, we will be looking at different ways to compare dates that aren't as prone to error. In the meantime, we were able to figure out the problem and both a temporary and long-term solution.
First, more details on the issue. It turns out that the values being stored in the TIMESTAMP field in the database were incorrect. We saw this by using the dump function and examining the bytes. If you look at the 5th byte in the output below, you'll see the hour (this is actually the hour + 1 so 5 is actually 4AM). For the values between 3AM and 4AM, you'll notice that the 5th byte is 3 which represents 2AM. 2 AM March 9, 2014 in EST doesn't exist - this is an incorrect time according to DST rules and Oracle's rules.
09-MAR-14 03.06.21.522000000 AM Typ=180 Len=11: 120,114,3,9,3,7,22,31,29,22,128
09-MAR-14 03.32.37.869000000 AM Typ=180 Len=11: 120,114,3,9,3,33,38,51,203,227,64
09-MAR-14 03.36.49.804000000 AM Typ=180 Len=11: 120,114,3,9,3,37,50,47,236,17,0
09-MAR-14 03.43.47.328000000 AM Typ=180 Len=11: 120,114,3,9,3,44,48,19,140,226,0
09-MAR-14 03.47.55.255000000 AM Typ=180 Len=11: 120,114,3,9,3,48,56,15,50,253,192
09-MAR-14 03.55.45.129000000 AM Typ=180 Len=11: 120,114,3,9,3,56,46,7,176,98,64
09-MAR-14 04.05.03.325000000 AM Typ=180 Len=11: 120,114,3,9,5,6,4,19,95,27,64
09-MAR-14 04.28.41.267000000 AM Typ=180 Len=11: 120,114,3,9,5,29,42,15,234,24,192
09-MAR-14 04.35.16.072000000 AM Typ=180 Len=11: 120,114,3,9,5,36,17,4,74,162,0
09-MAR-14 04.41.07.260000000 AM Typ=180 Len=11: 120,114,3,9,5,42,8,15,127,73,0
09-MAR-14 04.46.31.047000000 AM Typ=180 Len=11: 120,114,3,9,5,47,32,2,205,41,192
09-MAR-14 04.53.33.471000000 AM Typ=180 Len=11: 120,114,3,9,5,54,34,28,18,227,192
After much research and discussion, we zeroed in on the fact that our version of the Oracle JDBC driver (11.2.0.2) might've been inserting the bad values. Oracle's information page on 11.2.0.3 references a bug that looks like it's our issue: "9785135 DST conversion not correct using jdbc 11g timestamtz". We wrote a quick test class that inserts values from March 9, 2014 1:50 AM to 4:00 AM using both the 11.2.0.2 and 11.2.0.3 driver. Here's a snippet of what was inserted into the db:
DRIVER_V JAVA_DATE_AS_STRING ORACLE_TIMESTAMP DUMP(ORACLE_TIMESTAMP)
11.2.0.2.0/11/2 Sun Mar 09 01:50:00 EST 2014 09-MAR-14 01.50.00.000000000 AM Typ=180 Len=7: 120,114,3,9,2,51,1
11.2.0.2.0/11/2 Sun Mar 09 03:00:00 EDT 2014 09-MAR-14 03.00.00.000000000 AM Typ=180 Len=7: 120,114,3,9,3,1,1 --Invalid timestamp
11.2.0.3.0/11/2 Sun Mar 09 01:50:00 EST 2014 09-MAR-14 01.50.00.000000000 AM Typ=180 Len=7: 120,114,3,9,2,51,1
11.2.0.3.0/11/2 Sun Mar 09 03:00:00 EDT 2014 09-MAR-14 03.00.00.000000000 AM Typ=180 Len=7: 120,114,3,9,4,1,1 --Correct timestamp
You'll notice that the 5th byte of the timestamp on the second row for 3:00 AM is incorrect (3). This was inserted using the 11.2.0.2 version. The same value inserted with the 11.2.0.3 version can be found on the fourth line with the correct 5th byte (4).
The long term fix here is to update our JDBC driver. The short term fix here was to find the rows that have the bad values and run an update statement on them from SQL Plus to set the time again (using the same value but SQL Plus will convert them correctly).