SELECT '00000001', TO_TIMESTAMP('2022-05-17 09:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0002' FROM DUAL UNION ALL

SELECT '00000001', TO_TIMESTAMP('2022-05-17 09:15:00', 'YYYY-MM-DD HH24:MI:SS'), '0003' FROM DUAL UNION ALL

SELECT '00000001', TO_TIMESTAMP('2022-05-17 12:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0002' FROM DUAL UNION ALL

SELECT '00000001', TO_TIMESTAMP('2022-05-17 13:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0003' FROM DUAL UNION ALL

SELECT '00000001', TO_TIMESTAMP('2022-05-17 15:00:00', 'YYYY-MM-DD HH24:MI:SS'), '0004' FROM DUAL),

CALC AS (SELECT d.*,

EXTRACT(DAY FROM timestamp_oracle - LAG(timestamp_oracle) OVER(PARTITION BY shift_id ORDER BY timestamp_oracle)) * 24 * 60 +

EXTRACT(HOUR FROM timestamp_oracle - LAG(timestamp_oracle) OVER(PARTITION BY shift_id ORDER BY timestamp_oracle)) * 60 +

EXTRACT(MINUTE FROM timestamp_oracle - LAG(timestamp_oracle) OVER(PARTITION BY shift_id ORDER BY timestamp_oracle)) tm

FROM data d)

SELECT shift_id,

TRUNC(ROUND(SUM(CASE WHEN type_ <> '0003' then tm else null end))/ 60) || ':' ||

MOD(ROUND(SUM(CASE WHEN type_ <> '0003' then tm else null end)), 60) tot_tm

from calc

GROUP BY shift_id;