Calculate TIME Difference in ORACLE for same field in different row with output in HH:MM:SS format

Question

I want to calculate the time "Second row Date/Time(F3)" - "First row Date/Time(F2)", to get the difference time in ORACLE sql. I tried in my ways to use (extract,to_char)apply in LAG function to subtract the date but i unable to get the output result that i wanted as below. Appreciate if someone can help me on this issue. Thanks.

[The output that i wish to get][2]

SELECT stg_rmstmp_cj.ota_activity.ID,
stg_rmstmp_cj.ota_activity.LOT,stg_rmstmp_cj.ota_activity.TYPE,stg_rmstmp_cj.ota_activity.KEY, stg_rmstmp_cj.ota_activity.VALUE,A.DATE,
LAG(stg_rmstmp_cj.ota_activity.DATE) OVER (ORDER BY stg_rmstmp_cj.ota_activity.DATE) -stg_rmstmp_cj.ota_activity.DATE AS Different_time 
FROM stg_rmstmp_cj.ota_activity where A.ID='93K-100';

This is my latest encountered new error as below with add in partition but seem like it does solve the issue with attached the screen shot sample:

select EQP_ID,LOT_ID,ACTIVITY,ID,
TO_CHAR(MODIFIED_DATE,'yyyy-mm-dd hh24:mi:ss') dt,
TO_CHAR (DATE '1900-01-01' + ABS ( LAG(MODIFIED_DATE,1)OVER(ORDER BY MODIFIED_DATE) - MODIFIED_DATE), 'HH24:MI:SS')AS diff_withoutpartition,
TO_CHAR (DATE '1900-01-01' + ABS ( LAG(MODIFIED_DATE,1)OVER(PARTITION BY EQP_ID ORDER BY MODIFIED_DATE) - MODIFIED_DATE), 'HH24:MI:SS')AS diff_withpartition
from STG_RMSTMP_CJ.ota_activity where EQP_ID = '93K-P007' 

Answer 1

Try below. Here '1900-01-01' is dummy for TO_CHAR to work correctly.

WITH MAIN
     AS (SELECT TO_DATE ('20200515 19:11:54', 'yyyymmdd hh24:mi:ss') DAT1,
                TO_DATE ('20200515 18:11:54', 'yyyymmdd hh24:mi:ss') DAT2
           FROM DUAL)
SELECT DAT1,
       DAT2,
       TO_CHAR (DATE '1900-01-01' + ABS (DAT1 - DAT2), 'HH24:MI:SS')
  FROM MAIN

Same function with using LAG.

WITH MAIN
     AS (SELECT TO_DATE ('20200515 19:11:54', 'yyyymmdd hh24:mi:ss') DAT1,
                TO_DATE ('20200515 18:11:54', 'yyyymmdd hh24:mi:ss') DAT2
           FROM DUAL
           UNION ALL
           SELECT TO_DATE ('20200514 19:12:54', 'yyyymmdd hh24:mi:ss') DAT1,
                TO_DATE ('20200514 16:12:54', 'yyyymmdd hh24:mi:ss') DAT2
           FROM DUAL
           )
SELECT DAT1,
       DAT2,
       LAG(DAT1,1)OVER(ORDER BY DAT1) LAG,
       TO_CHAR (DATE '1900-01-01' + ABS ( LAG(DAT1,1)OVER(ORDER BY DAT1) - DAT1), 'HH24:MI:SS')
  FROM MAIN