Time difference between two timestamps - Oracle

0
votes

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.

4
sqloracletimestamp
what are the data types of your start_time and end_time columns? - Boneist
@Boniest, Thanks for your response, actually the below works but i want to get in hours and minutes. (CAST(( b.end_time ) AS DATE ) - CAST((b.start_time ) AS DATE )) * 24 * 60 AS Layove , (This resulting only in minutes) - Love_Is_God
BTW, TIMESTAMP(6) is datatype of both - Love_Is_God

4 Answers

0
votes

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))
0
votes

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
0
votes

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

db<>fiddle here

0
votes

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

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