SQLize Online / PHPize Online  /  SQLtest Online

CREATE OR REPLACE FUNCTION timestamp_diff ( start_time_in TIMESTAMP , end_time_in TIMESTAMP ) RETURN NUMBER AS l_days NUMBER; l_hours NUMBER; l_minutes NUMBER; l_seconds NUMBER; l_milliseconds NUMBER; BEGIN SELECT extract(DAY FROM end_time_in-start_time_in) , extract(HOUR FROM end_time_in-start_time_in) , extract(MINUTE FROM end_time_in-start_time_in) , extract(SECOND FROM end_time_in-start_time_in) INTO l_days, l_hours, l_minutes, l_seconds FROM dual; l_milliseconds := l_seconds*1000 + l_minutes*60*1000 + l_hours*60*60*1000 + l_days*24*60*60*1000; RETURN l_milliseconds; END; create table t (cid int, step char(3), cr_time timestamp); insert into t values (120, 'S02', '08-JUL-24 09.35.19.000 AM'), (120, 'S03', '08-JUL-24 01.35.19.000 PM'), (120, 'S04', '09-JUL-24 02.35.19.000 PM'), (121, 'S02', '09-JUL-24 07.35.19.000 AM'), (121, 'S03', '09-JUL-24 02.35.19.000 PM'), (122, 'S02', '10-JUL-24 10.35.19.000 AM'), (122, 'S03', '10-JUL-24 05.35.19.000 PM') ; with cte as( select cid, min(case when step = 'S02' then cr_time end) S02_time, min(case when step = 'S03' then cr_time end) S03_time from t where (CAST (cr_time as TIME) >= '8:30:00 AM' and CAST (cr_time as TIME) <= '4:30:00 PM') group by cid ) select *,timestamp_diff(S03_TIME-S02_TIME) as diff from cte where S02_time IS NOT NULL AND S03_time IS NOT NULL ;