Time difference between two timestamps

Just getting into Oracle for the first time. how to find the time difference between the below in Oracle.

to_char(CAST(b.start_time AS DATE),'dd-MON-yy hh24:mi:ss') as start_time    
to_char(CAST(b.end_time AS DATE),'dd-MON-yy hh24:mi:ss')  as end_time

I need to get the time difference in hours and minutes for end and start times, means (end_time - start_time).

Appreciate your help.

CodePudding user response：

Find the day, hour and minute components of the difference using EXTRACT and then concatenate them into a single string:

SELECT (EXTRACT(DAY FROM end_time - start_time)*24
         EXTRACT(HOUR FROM end_time - start_time))
       || ':'
       || TO_CHAR(EXTRACT(MINUTE FROM end_time - start_time), 'FM00')
         AS difference
FROM   table_name

Note: If you only parse the hours and minutes then you will not know if the difference is greater than 24 hours.

Which, for the sample data:

CREATE TABLE table_name (start_time TIMESTAMP(6), end_time TIMESTAMP(6) );

INSERT INTO table_name (start_time, end_time)
SELECT TRUNC(SYSTIMESTAMP), SYSTIMESTAMP FROM DUAL UNION ALL
SELECT TIMESTAMP '2022-01-01 00:00:00', TIMESTAMP '2022-01-03 00:00:00' FROM DUAL;

Outputs:

DIFFERENCE

14:15

48:00

DIFFERENCE
14:15
48:00

db<>fiddle here

CodePudding user response：

Simply do

b.end_time - b.start_time

Result is an INTERVAL DAY TO SECOND value, see INTERVAL

In order to get hours and minutes you can use either SUBSTR / REGEX_SUBSTR / REGEXP_REPLACE or EXTRACT

REGEXP_REPLACE((b.end_time - b.start_time), '.*(\d{2}:\d{2}):\d{2}.*', '\1')

EXTRACT(HOUR FROM (b.end_time - b.start_time))||':'||EXTRACT(minute FROM (b.end_time - b.start_time))

CodePudding user response：

You can subtract time in Oracle. Make sure your data type is in Datetime format and multiply it with 24 to get hours and 24*60 for minutes.

   (b.end_time - b.start_time) * 24    --For Hours
   (b.end_time - b.start_time) * 24*60 --For Minutes

CodePudding user response：

I have taking 1970 year and then adding this with the difference between start_date_time and end_date_time with the require format -> hh24:mi:ss

You can try this query:

SELECT
   TO_CHAR(TO_DATE('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')   (TO_DATE('2022-01-18 21:00', 'YYYY-MM-DD hh24:mi') - TO_DATE
            ('2022-01-18 19:10', 'YYYY-MM-DD hh24:mi')), 'hh24:mi:ss') AS human_readable_format
FROM
   DUAL;

Output