Time Difference in Oracle SQL
Hello,
You can find time difference between two times by using to_char and to_date sql functions. Use to_char(sysdate, ‘sssss’) in order to calculate seconds from midnight.
|
1 2 3 |
select to_char(sysdate, 'sssss') from dual; |
TO_CHAR(SYSDATE,’SSSSS’)
————————
42881
1 row selected.
We can also convert seconds to time with to_date(<seconds_past_midnight>, ‘sssss’).
|
1 2 3 |
select to_char(to_date(42881, 'sssss'), 'hh24:mi:ss') from dual; |
TO_CHAR(TO_DATE(42881,’SSSSS’),’HH24:MI:SS’)
——————————————–
11:54:41
1 row selected.
Combine both functions and calculate difference.
|
1 2 3 4 5 6 7 8 |
SELECT TO_CHAR (TO_DATE (ABS (end_time - start_time), 'sssss'), 'hh24:mi') as time_diff FROM (SELECT TO_NUMBER (TO_CHAR (TO_DATE ('10:55', 'HH24:MI'), 'SSSSS')) start_time, TO_NUMBER (TO_CHAR (TO_DATE ('19:24', 'HH24:MI'), 'SSSSS')) end_time FROM DUAL); |
TIME_DIFF
———–
08:29
1 row selected.