How to subtract times stored as strings in Oracle

Question

I have a scenario where I need to subtract one time from another.

In table column data type is varchar2

event_time_1 varchar2(10)
event_time_2 varchar2(10)

Data in table

event_time_1  event_time_2
10:01         10:04
10:02         10:06 

Now I need to calculate event_time_1 - event_time_2, but as both of them are varchar2, how can I achieve it?

Answer 1

Convert the strings to dates and then subtract to get either an interval or else the number of days difference (which you can multiple by 24*60 to get minutes):

SELECT event_time_1,
       event_time_2,
       (
         TO_DATE(event_time_2, 'HH24:MI')
         - TO_DATE(event_time_1, 'HH24:MI')
       ) DAY TO SECOND AS interval_diff,
       (
         TO_DATE(event_time_2, 'HH24:MI')
         - TO_DATE(event_time_1, 'HH24:MI')
       ) AS days_diff,
       (
         TO_DATE(event_time_2, 'HH24:MI')
         - TO_DATE(event_time_1, 'HH24:MI')
       ) * 24 * 60 AS minutes_diff
FROM   table_name

Which, for the sample data:

CREATE TABLE table_name (
  event_time_1 varchar2(10),
  event_time_2 varchar2(10)
);

INSERT INTO table_name (event_time_1, event_time_2)
SELECT '10:01', '10:04' FROM DUAL UNION ALL
SELECT '10:02', '10:06' FROM DUAL

Outputs:

EVENT_TIME_1	EVENT_TIME_2	INTERVAL_DIFF	DAYS_DIFF	MINUTES_DIFF
10:01	10:04	+00 00:03:00.000000	.002083333333333333333333333333333333333333	3
10:02	10:06	+00 00:04:00.000000	.002777777777777777777777777777777777777778	4

fiddle