Oracle 18c

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

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