由于 DST，Oracle 日期比较损坏 [英] 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 实际上是凌晨 4 点).对于 3AM 和 4AM 之间的值，您会注意到第 5 个字节是 3，表示 2AM.美国东部标准时间 2014 年 3 月 9 日凌晨 2 点不存在 - 根据 DST 规则和 Oracle 的规则.
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 引用了一个看起来像是我们的问题的错误:"9785135 使用 jdbc 11g timestamtz 的 DST 转换不正确".我们编写了一个快速测试类，它使用 11.2.0.2 和 11.2.0.3 驱动程序插入从 2014 年 3 月 9 日凌晨 1:50 到凌晨 4:00 的值.这是插入数据库的片段:
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 的第二行时间戳的第 5 个字节不正确 (3).这是使用 11.2.0.2 版本插入的.与 11.2.0.3 版本插入的相同值可以在第四行找到正确的第 5 个字节 (4).
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).
这篇关于由于 DST，Oracle 日期比较损坏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
