Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (NAME, OPERATION, TIMESTAMP) AS
SELECT 'A', 'START', DATE '2024-01-18' + INTERVAL '08:24:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'A', 'START', DATE '2024-01-18' + INTERVAL '08:27:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'A', 'START', DATE '2024-01-18' + INTERVAL '08:30:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'A', 'END',   DATE '2024-01-18' + INTERVAL '08:34:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'B', 'START', DATE '2024-01-18' + INTERVAL '09:28:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'B', 'END',   DATE '2024-01-18' + INTERVAL '09:32:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'B', 'START', DATE '2024-01-18' + INTERVAL '09:37:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'B', 'END',   DATE '2024-01-18' + INTERVAL '09:39:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'C', 'START', DATE '2024-01-18' + INTERVAL '10:14:00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 'C', 'END',   DATE '2024-01-18' + INTERVAL '10:18:00' HOUR TO SECOND FROM DUAL;
10 rows affected

SELECT name,
       start_event,
       start_time,
       end_event,
       end_time,
       end_time - start_time AS duration
FROM   table_name
       MATCH_RECOGNIZE(
         PARTITION BY name
         ORDER BY timestamp
         MEASURES
           FIRST(operation) AS start_event,
           FIRST(timestamp) AS start_time,
           LAST(operation)  AS end_event,
           LAST(timestamp)  AS end_time
         PATTERN (start_op+ end_op)
         DEFINE
           start_op AS operation = 'START',
           end_op   AS operation = 'END'
       )


NAME
START_EVENT
START_TIME
END_EVENT
END_TIME
DURATION


A
START
2024-01-18 08:24:00
END
2024-01-18 08:34:00
.006944444444444444444444444444444444444444

B
START
2024-01-18 09:28:00
END
2024-01-18 09:32:00
.002777777777777777777777777777777777777778

B
START
2024-01-18 09:37:00
END
2024-01-18 09:39:00
.001388888888888888888888888888888888888889

C
START
2024-01-18 10:14:00
END
2024-01-18 10:18:00
.002777777777777777777777777777777777777778

NAME	START_EVENT	START_TIME	END_EVENT	END_TIME	DURATION
A	START	2024-01-18 08:24:00	END	2024-01-18 08:34:00	.006944444444444444444444444444444444444444
B	START	2024-01-18 09:28:00	END	2024-01-18 09:32:00	.002777777777777777777777777777777777777778
B	START	2024-01-18 09:37:00	END	2024-01-18 09:39:00	.001388888888888888888888888888888888888889
C	START	2024-01-18 10:14:00	END	2024-01-18 10:18:00	.002777777777777777777777777777777777777778