By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table qrtz_triggers (id, trigger_type, end_time) as
select 1, 'SIMPLE', 8842852800000 from dual
union all
select 2, 'SIMPLE', 8843112000000 from dual
union all
select 3, 'SIMPLE', 1527109200000 from dual
3 rows affected
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;
ORA-01873: the leading precision of the interval is too small
select * from qrtz_triggers
where trigger_type = 'SIMPLE'
and end_time < power(2, 31) * 1000
and
SYS_EXTRACT_UTC(TIMESTAMP '1970-01-01 00:00:00.00 -03:00') + NUMTODSINTERVAL (end_time / 1000, 'SECOND' ) <= SYSTIMESTAMP + INTERVAL '7' DAY;
| ID | TRIGGER_TYPE | END_TIME |
|---|---|---|
| 3 | SIMPLE | 1527109200000 |
select * from qrtz_triggers
where trigger_type = 'SIMPLE'
and
SYS_EXTRACT_UTC(TIMESTAMP '1970-01-01 00:00:00.00 -03:00')
+ NUMTODSINTERVAL (least(end_time / 1000, power(2, 31) - 1), 'SECOND')
<= SYSTIMESTAMP + INTERVAL '7' DAY;
| ID | TRIGGER_TYPE | END_TIME |
|---|---|---|
| 3 | SIMPLE | 1527109200000 |
select * from qrtz_triggers
where trigger_type = 'SIMPLE'
and
TIMESTAMP '1970-01-01 00:00:00.00 UTC'
+ NUMTODSINTERVAL (least(end_time / 1000, power(2, 31) - 1), 'SECOND')
<= SYSTIMESTAMP + INTERVAL '7' DAY;
| ID | TRIGGER_TYPE | END_TIME |
|---|---|---|
| 3 | SIMPLE | 1527109200000 |
select SYSTIMESTAMP + INTERVAL '7' DAY - TIMESTAMP '1970-01-01 00:00:00.00 UTC'
from dual;
| SYSTIMESTAMP+INTERVAL'7'DAY-TIMESTAMP'1970-01-0100:00:00.00UTC' |
|---|
| +000018732 08:26:41.869910000 |
with t (epoch) as (
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
)
)
select 1000 * epoch from t
| 1000*EPOCH |
|---|
| 1618475201872.477 |
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
)
)
| ID | TRIGGER_TYPE | END_TIME |
|---|---|---|
| 3 | SIMPLE | 1527109200000 |