By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE db (ID, UnixDateTime, OrderNumber) AS
SELECT 1, 1662615688, 100 FROM DUAL UNION ALL
SELECT 2, 1662615752, 100 FROM DUAL UNION ALL
SELECT 3, 1662615765, 100 FROM DUAL UNION ALL
SELECT 4, 1662615859, 100 FROM DUAL UNION ALL
SELECT 5, 1662615987, 100 FROM DUAL UNION ALL
SELECT 6, 1662616031, 100 FROM DUAL;
6 rows affected
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZR';
SELECT id,
unixdatetime,
ordernumber,
date_time,
next_unixdatetime,
next_unixdatetime - unixdatetime AS diff,
CASE cls
WHEN 'WITHIN_100' THEN 1
ELSE 0
END AS within_100
from (
select distinct
ID,
UnixDateTime,
OrderNumber,
TIMESTAMP '1970-01-01 00:00:00 UTC' + UnixDateTime * INTERVAL '1' SECOND
AS Date_Time
from DB
where TIMESTAMP '1970-01-01 00:00:00 UTC' + UnixDateTime * INTERVAL '1' SECOND
> SYSTIMESTAMP - INTERVAL '2' DAY
)
MATCH_RECOGNIZE(
ORDER BY unixdatetime
MEASURES
NEXT(unixdatetime) AS next_unixdatetime,
classifier() AS cls
ALL ROWS PER MATCH
PATTERN (within_100* any_row)
DEFINE
within_100 AS NEXT(unixdatetime) < unixdatetime + 100
) m
| ID | UNIXDATETIME | ORDERNUMBER | DATE_TIME | NEXT_UNIXDATETIME | DIFF | WITHIN_100 |
|---|---|---|---|---|---|---|
| 1 | 1662615688 | 100 | 2022-09-08 05:41:28.000000000 UTC | 1662615752 | 64 | 1 |
| 2 | 1662615752 | 100 | 2022-09-08 05:42:32.000000000 UTC | 1662615765 | 13 | 1 |
| 3 | 1662615765 | 100 | 2022-09-08 05:42:45.000000000 UTC | 1662615859 | 94 | 1 |
| 4 | 1662615859 | 100 | 2022-09-08 05:44:19.000000000 UTC | 1662615987 | 128 | 0 |
| 5 | 1662615987 | 100 | 2022-09-08 05:46:27.000000000 UTC | 1662616031 | 44 | 1 |
| 6 | 1662616031 | 100 | 2022-09-08 05:47:11.000000000 UTC | null | null | 0 |