由于 DST，Oracle 日期比较被破坏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22305466/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle date compare broken because of DST
提问by Chris Williams
We've been debugging an issue with a SQL query executed from an app server running Java via Hibernate. The error:
我们一直在调试通过 Hibernate 从运行 Java 的应用服务器执行 SQL 查询的问题。错误：
[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.
我们已经能够将其缩小到下面的简单 SQL。
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_TIMESTAMPcolumn is defined as TIMESTAMP(6).
该MY_TIMESTAMP列定义为TIMESTAMP(6)。
FWIW, if we change the comparison in the SQL above from >=to <=, the query works.
FWIW，如果我们将上面 SQL 中的比较从>=更改为<=，则查询有效。
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:
此外，我们已经通过 MyBatis 运行的类似查询看到了这个问题，错误如下：
### 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.
更新：Windows 上的一位队友通过取消选中“自动调整夏令时时钟”更改了她的 Windows 日期和时间设置，然后打开了一个新的 SQLDeveloper 实例。第二个实例能够毫无问题地运行查询，但第一个（使用旧的 DST 设置）仍然失败。
采纳答案by Chris Williams
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.
感谢 kordirko 写得非常详细。我认为在未来，我们将寻找不同的方法来比较不容易出错的日期。与此同时，我们能够找出问题以及临时和长期解决方案。
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.
首先，关于这个问题的更多细节。事实证明，存储在数据库 TIMESTAMP 字段中的值不正确。我们通过使用转储函数和检查字节看到了这一点。如果您查看下面输出中的第 5 个字节，您将看到小时（这实际上是小时 + 1，所以 5 实际上是凌晨 4 点）。对于 3AM 和 4AM 之间的值，您会注意到第 5 个字节是 3，代表 2AM。东部标准时间 2014 年 3 月 9 日凌晨 2 点不存在 - 根据 DST 规则和Oracle规则，这是不正确的时间。
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.3references 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:
经过大量研究和讨论后，我们发现我们的 Oracle JDBC 驱动程序版本 (11.2.0.2) 可能插入了错误值。Oracle 11.2.0.3 上的信息页面引用了一个看起来像是我们的问题的错误：“9785135 DST 转换不正确使用 jdbc 11g timestamtz”。我们编写了一个快速测试类，它使用 11.2.0.2 和 11.2.0.3 驱动程序从 2014 年 3 月 9 日凌晨 1:50 到凌晨 4:00 插入值。这是插入到数据库中的内容的片段：
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).
您会注意到第二行 3:00 AM 时间戳的第 5 个字节不正确 (3)。这是使用 11.2.0.2 版本插入的。可以在第四行找到与 11.2.0.3 版本相同的值，并使用正确的第 5 个字节 (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).
这里的长期修复是更新我们的 JDBC 驱动程序。这里的短期修复是找到具有错误值的行，然后从 SQL Plus 对它们运行更新语句以再次设置时间（使用相同的值，但 SQL Plus 会正确转换它们）。
回答by krokodilko
To avoid this error, consider using an explicit cast of the expression in the where clause to a timestamp type (timestamp without timezone), in this way:
为避免此错误，请考虑将 where 子句中的表达式显式转换为时间戳类型（不带时区的时间戳），如下所示：
select *
from MY_TABLE T
where T.MY_TIMESTAMP >= cast(CURRENT_TIMESTAMP - interval '1' hour As timestamp );
Alternatively you can explicitely set the session time zone to, for example '-05:00' - for New York standard (winter) time,
using ALTER SESSION time_zone = '-05:00', or by setting ORA_SDTZ environment variable in all client's environments,
see this link for details: http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG263
But it also depends on what reallyis stored in the timestamp column in the table, for example what a timestamp 2014-07-01 15:00:00represents in fact, is it a "winter time" or a "summer time" ?
或者，您可以将会话时区显式设置为例如“-05:00” - 对于纽约标准（冬季）时间，
使用ALTER SESSION time_zone = '-05:00'，或通过在所有客户端环境中设置 ORA_SDTZ 环境变量，
请参阅此链接了解详细信息：http: //docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG263
但也要看表中timestamp列中真正存储的是什么，例如timestamp2014-07-01 15:00:00实际上代表什么，是是“冬令时”还是“夏令时”？
CURRENT_TIMESTAMPfunction returns a value of datatype TIMESTAMP WITH TIME ZONE
see this link: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions037.htm
While comparing timestamps and dates, Oracle implicitely converts the data to the more precise data type using the session time zone !
See this link --> http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG251
In our particular case, Oracle casts timestampcolumn to the timestamp with time zonetype.
Oracle determines a session timezone from the client environment.
You can determine current session timezone using this query:
CURRENT_TIMESTAMP函数返回数据类型 TIMESTAMP WITH TIME ZONE 的值，
请参阅此链接：http: //docs.oracle.com/cd/B19306_01/server.102/b14200/functions037.htm
在比较时间戳和日期时，Oracle 隐式地将数据转换为使用会话时区更精确的数据类型！
请参阅此链接 --> http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLPG251
在我们的特定情况下，Oracle 将timestamp列转换为timestamp with time zone类型。
Oracle 根据客户端环境确定会话时区。
您可以使用此查询确定当前会话时区：
select sessiontimezone from dual;
For example on my PC (Win 7), when the option ""Automatically adjust clock for Daylight Saving Time" is checked, this query returns (under SQLDeveloper):
For example on my PC (Win 7), when the option ""Automatically adjust clock for Daylight Saving Time" is checked, this query returns (under SQLDeveloper):
SESSIONTIMEZONE
---------------
Europe/Belgrade
When i uncheck this option in Windows and then restart SQLDeveloper, it gives:
当我在 Windows 中取消选中此选项然后重新启动 SQLDeveloper 时，它会给出：
SESSIONTIMEZONE
---------------
+01:00
The former session timezone is a timezone with a region name, for which Oracle uses the Daylight Saving Time rules for this region in date calculations:
前一个会话时区是一个带有区域名称的时区，Oracle在日期计算中使用该区域的夏令时规则：
alter session set time_zone = 'Europe/Belgrade';
select cast( timestamp '2014-01-29 01:30:00' as timestamp with time zone ) As x,
cast( timestamp '2014-05-29 01:30:00' as timestamp with time zone ) As y
from dual;
session SET altered.
X Y
---------------------------- ----------------------------
2014-01-29 01:30:00 EUROPE/B 2014-05-29 01:30:00 EUROPE/B
ELGRADE ELGRADE
The latter timezone uses a fixed offset "+01:00" (always the "Winter time"), and Oracle does not apply any DST rules for it, it simply adds the fixed offset.
后一个时区使用固定偏移量“+01:00”（始终为“冬季时间”），Oracle 不对其应用任何 DST 规则，它只是添加固定偏移量。
alter session set time_zone = '+01:00';
select cast( timestamp '2014-01-29 01:30:00' as timestamp with time zone ) As x,
cast( timestamp '2014-05-29 01:30:00' as timestamp with time zone ) As y
from dual;
session SET altered.
X Y
---------------------------- ----------------------------
2014-01-29 01:30:00 +01:00 2014-05-29 01:30:00 +01:00
Please note, for curiosity's sake, that Yresults in the above represent two different times !!!014-05-29 01:30:00 EUROPE/BELGRADEis not the same as: 2014-05-29 01:30:00 +01:00
but actually this:014-05-29 01:30:00 EUROPE/BELGRADEis equal to: 2014-05-29 01:30:00 +02:00
The above is only to make you aware of how simple "box un-checking" could affect your queries, and where to dig for a reason when users complain "this query worked fine in January, but gave wrong results in July".
请注意，出于好奇，Y上面的结果代表两个不同的时间！！！014-05-29 01:30:00 EUROPE/BELGRADE不一样：2014-05-29 01:30:00 +01:00
但实际上这：014-05-29 01:30:00 EUROPE/BELGRADE等于：2014-05-29 01:30:00 +02:00
以上只是让您了解“取消选中框”可能会影响您的查询的简单程度，以及当用户抱怨“此查询有效”时应该挖掘的原因一月份还好，但七月份给出了错误的结果”。
And still on the topic of ORA-01878 - let say my session is EUROPE/Warsawand my table containts this timestamp (without time zone)
仍然是关于 ORA-01878 的主题 - 假设我的会话是EUROPE/Warsaw并且我的表包含这个时间戳（没有时区）
'TIMESTAMP'2014-03-30 2:30:00'
Note that in my region the DST change, in 2014 year, occurs on 30 of march at 2:00 a.m.
It simply means that on march 30, at 2:00 at night, I must wake up and shift my watch forward from 2:00 to 3:00 ;)
请注意，在我所在的地区，2014 年的夏令时变化发生在 3 月 30 日凌晨 2:00
这只是意味着在 3 月 30 日，晚上 2:00，我必须醒来并将我的手表从 2 向前移动： 00 到 3:00 ;)
alter session set time_zone = 'Europe/Warsaw';
select cast( TIMESTAMP'2014-03-30 2:30:00' as timestamp with time zone ) As x
from dual;
SQL Error: ORA-01878: podane pole nie zosta?o znalezione w dacie-godzinie ani w interwale
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.
Oracle knows, that this timestamp is not valid in my regionaccording to DST rules, because there is no time 2:30 on 30 of march - at 2:00 the clock is moved to 3:00, and there is no time 2:30. Therefore Oracle throws the error ORA-01878.
However this query works perfectly fine:
Oracle 知道，根据 DST 规则，此时间戳在我的地区无效，因为 3 月 30 日没有时间 2:30 - 在 2:00 时钟移至 3:00，并且没有时间 2： 30. 因此 Oracle 抛出错误 ORA-01878。
但是这个查询工作得很好：
alter session set time_zone = '+01:00';
select cast( TIMESTAMP'2014-03-30 2:30:00' as timestamp with time zone ) As x
from dual;
session SET altered.
X
----------------------------
2014-03-30 02:30:00 +01:00
And this is a reason of this error - your table contains timestamps like 2014-03-09 2:30or so (for New York, where DST shifts occur on 9 of March and 2 of November), and Oracle doesn't know how to convert them from timestamp (without TZ) to timestamp with TZ.
这是此错误的原因 - 您的表包含类似2014-03-09 2:30左右的时间戳（对于纽约，DST 变化发生在 3 月 9 日和 11 月 2 日），并且 Oracle 不知道如何从时间戳转换它们（没有 TZ ) 用 TZ 标记时间戳。
The last question - why the query with >=doesn't work, but the query with <=works fine ?
They work/don'n work, because SQLDeveloper returns only first 50 rows (maybe 100 ? It depends on settings). The query doesn't read the whole table, it stops when first 50(100) rows are fetched.
Change the "working" query to, for example:
最后一个问题 - 为什么查询 with>=不起作用，但查询 with<=工作正常？
它们工作/不工作，因为 SQLDeveloper 只返回前 50 行（可能是 100 行？这取决于设置）。查询不会读取整个表，它会在获取前 50(100) 行时停止。
将“工作”查询更改为，例如：
select sum( EXTRACT(HOUR from MY_TIMESTAMP) ) from MY_TABLE
where MY_TIMESTAMP <= (CURRENT_TIMESTAMP - interval '1' hour );
This force the query to read all rows in the table, and the error will appear, I'am 100% sure.
这会强制查询读取表中的所有行，并且会出现错误，我 100% 肯定。
