--ORA-01873: the leading precision of the interval is too small

Question

I faced ORA-01873: the leading precision of the interval is too small issue ,found that some values in my table exceeds max value 2147483647000 for numtodsinterval()

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

can i have a check end_time < 2147483647000 or there is any other way to check this .

You can add that filter on the value, but it would be more reliable to handle them in a case expression so you can control when it is evaluated. What date are those very large values supposed to represent - really 2250? - and how do you want to use/interpret them? (Also: [related](https://stackoverflow.com/a/37772644/266304).) — Alex Poole, Apr 08 '21 at 07:32
Or if your end time column is indexed, it would be more efficient to convert the target time - now + 7 days - to an epoch number, which would both avoid the conversion issue and be more efficient. — Alex Poole, Apr 08 '21 at 07:40
Why `SYS_EXTRACT_UTC(TIMESTAMP '1970-01-01 00:00:00.00 -03:00')`? Simply use `TIMESTAMP '1970-01-01 00:00:00.00 UTC'` — Wernfried Domscheit, Apr 08 '21 at 08:25

score 1 · Answer 1 · answered Apr 08 '21 at 08:30

1

You could use this one:

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

answered Apr 08 '21 at 08:30

Wernfried Domscheit

54,457
9
76
110

score 0 · Accepted Answer · answered Apr 08 '21 at 08:25

You could just add a filter:

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

but you can't generally (or easily) control the order that Oracle evaluates the predicates, so it might still throw the same error - and inconsistently. You could instead use a case expression to control when the conversion happens, or a bit more simply here - as you are not looking very far ahead - treat any very large value as if it was the 2^31-1 limit:

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

Your use of sys_extract_utc here is odd, by the way; you're specifying a time with a 3 hour offset then converting back to UTC, when you could just specify a UTC time in the first place:

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

However, your comparison is having to convert every value in your table to a timestamp before comparing and filtering, which will prevent an index being used (unless you have a function-based index already, which seems unlikely when the conversion is failing). It would be more efficient to convert the target time - 7 days in the future - to an epoch number, and then compare the column values against that:

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<>fiddle

Depending on the precision you're looking for, you might need to take leap seconds into consideration, but that's another topic.

--ORA-01873: the leading precision of the interval is too small

2 Answers2