由于DST,Oracle日期比较坏

[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.

select * 
from MY_TABLE T
where T.MY_TIMESTAMP >= (CURRENT_TIMESTAMP - interval '1' hour );

当我们在同一个数据库中运行时,我们会收到错误：

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中的比较从> =更改为< =,则查询工作. 我们认为这与时间变化有关(我们在美国/纽约州),但是我们正在尝试通过我们的调试找出从哪里走出去的问题. 此外,我们已经看到一个类似的查询通过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

更新：Windows上的一名同事通过取消选中“自动调整夏令时间”,然后打开一个新的SQLDeveloper实例来更改Windows日期和时间设置.第二个实例能够运行查询而没有任何问题,但第一个(具有旧的DST设置)仍然失败.

select * 
from MY_TABLE T
where T.MY_TIMESTAMP >= cast(CURRENT_TIMESTAMP - interval '1' hour As timestamp );

或者,您可以使用ALTER SESSION time_zone =’-05：00’将纽约标准(冬季)时间的会话时区设置为例如“-05：00”,或者通过在所有客户端中设置ORA_SDTZ环境变量环境,请参阅此链接了解详情：http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch4datetime.htm#NLSPG263
但这也取决于表中时间戳列中存储的内容,例如2014-07-01 15:00:00的时间戳实际上代表的是“冬天”还是“夏令时”？

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#NLSPG251

在我们特定的情况下,Oracle将时间戳列转换为带有时区类型的时间戳.

Oracle从客户端环境确定会话时区.
您可以使用此查询确定当前会话时区：

select sessiontimezone from dual;

例如在我的PC(Win 7)上,当选中“自动调整夏时制时钟”选项时,此查询返回(在SQLDeveloper下)：

SESSIONTIMEZONE                                                           
---------------
Europe/Belgrade

当我在Windows中取消选中此选项,然后重新启动SQLDeveloper时,它给出：

SESSIONTIMEZONE                                                           
---------------
+01:00

前会话时区是具有区域名称的时区,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

后一个时区使用固定偏移量“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

请注意,出于好奇的缘故,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
以上只是为了让你知道如何简单的“盒子取消检查”可能会影响你的查询,当用户抱怨“这个查询在1月份工作正常,但在7月错误的结果”时挖掘的原因.

而且仍然是ORA-01878的主题 – 让我的会话是欧洲/华沙,我的表格包含这个时间戳(没有时区)

'TIMESTAMP'2014-03-30 2:30:00'

请注意,在我的地区,DST在2014年的变化发生在凌晨3点30分.
这只是意味着在3月30日晚上2点,我必须醒来,把我的手表从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.

甲骨文知道,这个时间戳记根据DST规则在我的区域无效,因为30时30分没有时间2点,在2点钟时钟移动到3点,没有时间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

这是这个错误的原因 – 您的表格包含时间戳,如2014-03-09 2:30左右(对于纽约,DST转移发生在3月9日和11月2日),Oracle不知道如何将它们从时间戳(不含TZ)转换为带有TZ的时间戳.

最后一个问题 – 为什么> =的查询不起作用,但< =工作正常的查询？
他们工作/不工作,因为SQLDeveloper只返回前50行(可能是100,这取决于设置).查询不读整个表,当前50(100)行被取出时,它停止.
将“工作”查询更改为,例如：

select sum( EXTRACT(HOUR from MY_TIMESTAMP) ) from MY_TABLE 
where MY_TIMESTAMP <= (CURRENT_TIMESTAMP - interval '1' hour );

这迫使查询读取表中的所有行,并且将出现错误,我确定100％.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!