——ORA-01873:区间领先精度太小

Question

我面对ORA-01873：间隔的领先精度太小的问题，发现我的表中的一些值超过了numtodsinterval((的最大值2147483647000

select * from qrtz_triggers 
where trigger_type = 'SIMPLE'
and
SYS_EXTRACT_UTC(TIMESTAMP '1970-01-01 00:00:00.00 -03:00') + NUMTODSINTERVAL (end_time / 1000, 'SECOND' ) <= SYSTIMESTAMP + INTERVAL '7' DAY;

end_time
8842852800000
8843112000000
1527109200000

您可以使用这个：

select * 
from qrtz_triggers 
where trigger_type = 'SIMPLE'
and
TIMESTAMP '1970-01-01 00:00:00.00 UTC' + 8842852800000 /1000/60/60/24 * INTERVAL '1' DAY <= SYSTIMESTAMP + INTERVAL '7' DAY

您可以添加一个过滤器：

where trigger_type = 'SIMPLE'
and end_time < power(2, 31) * 1000
and
SYS_EXTRACT_UTC ...

但您通常(或很容易(无法控制Oracle评估谓词的顺序，因此它可能仍然会抛出相同的错误——而且不一致。相反，您可以使用case表达式来控制转换何时发生，或者在这里更简单一点——因为您没有展望太远——将任何非常大的值视为2^31-1限制：

+ NUMTODSINTERVAL (least(end_time / 1000, power(2, 31) - 1), 'SECOND')

顺便说一句，你在这里使用sys_extract_utc很奇怪；您指定了一个偏移量为3小时的时间，然后转换回UTC，而您可以首先指定一个UTC时间：

TIMESTAMP '1970-01-01 00:00:00.00 UTC'
+ NUMTODSINTERVAL (least(end_time / 1000, power(2, 31) - 1), 'SECOND')

然而，在比较和筛选之前，您的比较必须将表中的每个值转换为时间戳，这将阻止使用索引(除非您已经有了基于函数的索引，当转换失败时，这似乎不太可能(。将目标时间(未来7天(转换为历元数，然后将列值与之进行比较会更有效：

select * from qrtz_triggers 
where trigger_type = 'SIMPLE'
and end_time <= 1000 * (
select 24 * 60 * 60 * extract (day from i)
+ 60 * 60 * extract (hour from i)
+ 60 * extract (minute from i)
+ extract (second from i)
from (
select SYSTIMESTAMP + INTERVAL '7' DAY - TIMESTAMP '1970-01-01 00:00:00.00 UTC' as i
from dual
)
)

db<gt；小提琴

根据你想要的精度，你可能需要考虑闰秒，但这是另一个话题。