Oracle日期比较由于DST而中断 [英] Oracle date compare broken because of DST
问题描述
我们一直在调试通过Hibernate从运行Java的应用服务器执行的SQL查询的问题.错误:
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
我们已经能够将其缩小到下面的简单SQL.
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.
MY_TIMESTAMP列定义为TIMESTAMP(6).
FWIW，如果我们将上面SQL中的比较从>=更改为<=，则查询有效.
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.
此外，我们已经通过MyBatis运行类似的查询时看到了此问题，并且错误如下所示:
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
更新:Windows上的一个队友通过取消选中自动调整夏令时的时钟"来更改了她的Windows日期和时间设置，然后打开了一个新的SQLDeveloper实例.第二个实例能够运行查询而没有任何问题，但是第一个实例(具有旧的DST设置)仍然失败.
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.
推荐答案
感谢kordirko进行了非常详细的介绍.我认为，将来，我们将寻找不同的方法来比较不太容易出错的日期.同时，我们能够找出问题所在以及临时和长期的解决方案.
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.
首先，有关此问题的更多详细信息.事实证明，数据库中TIMESTAMP字段中存储的值不正确.我们通过使用转储功能并检查了字节了解了这一点.如果查看下面输出中的第5个字节，您会看到小时(这实际上是小时+ 1，所以5实际上是4AM).对于3AM到4AM之间的值，您会注意到第5个字节是3，表示2AM. 2014年3月9日凌晨2点(美国东部标准时间)不存在-根据DST规则和
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
经过大量研究和讨论，我们确定了我们的Oracle JDBC驱动程序版本(11.2.0.2)可能一直在插入错误值. Oracle的 11.2.0.3上的信息页面引用了一个看起来像是我们的问题的错误:使用jdbc 11g timestamtz的9785135 DST转换不正确.我们编写了一个快速测试类，使用11.2.0.2和11.2.0.3驱动程序插入了从2014年3月9日上午1:50到4:00 AM的值.这是插入到数据库中的代码片段:
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
您会注意到第二行的3:00 AM时间戳的第5个字节不正确(3).这是使用11.2.0.2版本插入的.可以在第四行找到正确的第五个字节(4)，找到与11.2.0.3版本插入的值相同的
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).
这里的长期解决方法是更新我们的JDBC驱动程序.此处的短期修复是找到具有错误值的行，然后从SQL Plus对它们执行更新语句以再次设置时间(使用相同的值，但SQL Plus可以正确转换它们).
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).
这篇关于Oracle日期比较由于DST而中断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
