I am trying to get difference between 2 date columns in HH:MM:SS. Currently I have the below query which is returning the difference in HH:MM:SS. The problem with the below query is even if there is a difference of 1 day and 13 hours, it is just giving the difference as 13:00:00. But I want output something like this 37:20:56. Could someone please help in achieving this.
(select(CASE WHEN Tab1 .DEPARTURE_TIMESTAMP IS NOT NULL AND Tab1.ARRIVAL_TIMESTAMP IS NOT NULL AND
Tab1.ARRIVAL_TIMESTAMP < Tab1.DEPARTURE_TIMESTAMP THEN
to_char(lpad(trunc(24*mod(Tab1.DEPARTURE_TIMESTAMP - Tab1.ARRIVAL_TIMESTAMP,1)), 2,0)) || ':' ||
to_char(lpad(trunc(mod(mod(Tab1.DEPARTURE_TIMESTAMP - Tab1.ARRIVAL_TIMESTAMP,1)*24,1)*60),2,0)) ||':' ||
to_char(lpad(trunc(mod(mod(mod(Tab1.DEPARTURE_TIMESTAMP -Tab1.ARRIVAL_TIMESTAMP,1)*24,1)*60,1)*60),2,0)) ELSE '' END) from (
select * from TABLE1 where txn_id=SJ.SERVICE_JOB_ID
order by last_modified_date desc
)Tab1 where rownum=1)as DURATION
date1 - date2 returns the number of days difference, so a simple trunc(date1-date2)*24 adds the number of hours
SQL> create table t ( df date, dt date );
Table created.
SQL>
SQL> insert into t values ( sysdate, sysdate + 0.2 );
1 row created.
SQL> insert into t values ( sysdate, sysdate + 2.2 );
1 row created.
SQL>
SQL> select
2 to_char(lpad(trunc(trunc(dt-df)*24+24*mod(dt - df,1)), 2,0)) || ':' ||
3 to_char(lpad(trunc(mod(mod(dt - df,1)*24,1)*60),2,0)) ||':' ||
4 to_char(lpad(trunc(mod(mod(mod(dt - df,1)*24,1)*60,1)*60),2,0))
5 from t;
TO_CHAR(LPAD(TRUNC(TRUNC(D
--------------------------
04:48:00
52:48:00