Find the difference between two dates in hours and minutes

Question

I am trying to come up with a way to calculate the difference between two dates in hours and minutes.

I have a table with two columns Start Date and TimeStamp:

Start Date              Timestamp
--------------------    --------------------
05/JAN/2016 05:30:00    01/JAN/2016 10:02:29
30/JAN/2016 06:10:00    18/JAN/2016 19:24:00
23/JAN/2016 06:10:00    08/JAN/2016 10:46:00
05/JAN/2016 05:30:00    30/DEC/2015 16:07:00
23/JAN/2016 06:10:00    08/JAN/2016 12:18:05
01/JAN/2016 14:10:00    16/DEC/2015 16:36:56
01/JAN/2016 14:10:00    16/DEC/2015 11:41:00
03/JAN/2016 05:15:00    02/JAN/2016 11:23:15
03/JAN/2016 05:15:00    02/JAN/2016 07:52:00

I use the query:

select ROUND(RM_LIVE.CRWGNDACTTIME.GNDACTSTARTRM_LIVE.TRANSACTIONLOG.TIMESTAMP,2)
                       AS "Difference"
  from Transaction;

The query result is:

0.002721428571428571428571428571428571428571
0.008178571428571428571428571428571428571429
0.0105785714285714285714285714285714285714
0.003971428571428571428571428571428571428571

Expected result:

133:23
 91:28
355:24
353:52
274:46

I got that expected result in Excel using this formula:

= MAX(T982+U982,W982+V982) - MIN(T982+U982,W982+V982)

How can I get the same result in Oracle SQL?

CASE
  WHEN trunc(24 * abs(RM_LIVE.TRANSACTIONLOG.TIMESTAMP
- RM_LIVE.CRWGNDACTTIME.GNDACTSTART))
  ||':'|| lpad(round(60 * mod(24 * abs(RM_LIVE.TRANSACTIONLOG.TIMESTAMP
- RM_LIVE.CRWGNDACTTIME.GNDACTSTART), 1)), 2, '0')  <= '11:00' THEN     'LESS'
ELSE 'MORE'
END AS "mORE/LESS",


386:29  1055    01-JAN-16   16-DEC-15   MORE
**102:41    1055    08-NOV-15   04-NOV-15   LESS**
381:33  1055    01-JAN-16   16-DEC-15   MORE
176:45  1055    20-NOV-15   12-NOV-15   MORE
**119:54    1055    08-NOV-15   03-NOV-15   LESS** 

Answer 1

I've shown a couple of variations with explanations in this answer, but it seems to be doing slightly more than you want - you don't want to see the seconds - and doesn't allow more than 100 hours.

The simplest way to get the output you want is with:

trunc(24 * (RM_LIVE.CRWGNDACTTIME.GNDACTSTART
    - RM_LIVE.TRANSACTIONLOG.TIMESTAMP))
  ||':'|| lpad(round(60 * mod(24 * (RM_LIVE.CRWGNDACTTIME.GNDACTSTART
    - RM_LIVE.TRANSACTIONLOG.TIMESTAMP), 1)), 2, '0')
as difference

The first part gets the whole number of hours, which is similar to a method you added in a comment, but truncating instead of rounding to only get the whole hours. Then there's a colon separator. Then the minutes are calculated by getting the remainder from the hours calculation - via mod() - which is the fractional number of hours, and multiplying that by 60. The lpad() adds a leading zero to the number of minutes, but you coudl use to_char() instead.

If you have a mix of ranges where timestamp could be before or after the start time then you can use the abs() function to always get a positive result.

trunc(24 * abs(RM_LIVE.CRWGNDACTTIME.GNDACTSTART
    - RM_LIVE.TRANSACTIONLOG.TIMESTAMP))
  ||':'|| lpad(round(60 * mod(24 * abs(RM_LIVE.CRWGNDACTTIME.GNDACTSTART
    - RM_LIVE.TRANSACTIONLOG.TIMESTAMP), 1)), 2, '0')
as difference

As a demo with your data mocked up in a single table:

create table your_table(id, start_time, timestamp) as
  select 1, to_date ('05/JAN/2016 05:30:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('01/JAN/2016 10:02:29', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 2, to_date ('30/JAN/2016 06:10:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('18/JAN/2016 19:24:00', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 3, to_date ('23/JAN/2016 06:10:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('08/JAN/2016 10:46:00', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 4, to_date ('05/JAN/2016 05:30:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('30/DEC/2015 16:07:00', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 5, to_date ('23/JAN/2016 06:10:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('08/JAN/2016 12:18:05', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 6, to_date ('01/JAN/2016 14:10:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('16/DEC/2015 16:36:56', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 7, to_date ('01/JAN/2016 14:10:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('16/DEC/2015 11:41:00', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 8, to_date ('03/JAN/2016 05:15:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('02/JAN/2016 11:23:15', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 9, to_date ('03/JAN/2016 05:15:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('02/JAN/2016 07:52:00', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 10, to_date ('16/JAN/2016 11:15:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('16/JAN/2016 12:44:00', 'DD/MON/YYYY HH24:MI:SS') from dual
  union all select 11, to_date ('16/JAN/2016 11:15:00', 'DD/MON/YYYY HH24:MI:SS'), to_date('16/JAN/2016 12:50:00', 'DD/MON/YYYY HH24:MI:SS') from dual;

The equivalent query:

select start_time, timestamp, trunc(24 * abs(start_time - timestamp))
    ||':'|| lpad(round(60 * mod(24 * abs(start_time - timestamp), 1)), 2, '0')
    as difference
from your_table
order by id;

START_TIME          TIMESTAMP           DIFFERENCE
------------------- ------------------- ----------
2016-01-05 05:30:00 2016-01-01 10:02:29 91:28     
2016-01-30 06:10:00 2016-01-18 19:24:00 274:46    
2016-01-23 06:10:00 2016-01-08 10:46:00 355:24    
2016-01-05 05:30:00 2015-12-30 16:07:00 133:23    
2016-01-23 06:10:00 2016-01-08 12:18:05 353:52    
2016-01-01 14:10:00 2015-12-16 16:36:56 381:33    
2016-01-01 14:10:00 2015-12-16 11:41:00 386:29    
2016-01-03 05:15:00 2016-01-02 11:23:15 17:52     
2016-01-03 05:15:00 2016-01-02 07:52:00 21:23     
2016-01-16 11:15:00 2016-01-16 12:44:00 1:29      
2016-01-16 11:15:00 2016-01-16 12:50:00 1:35      

---

You can't easily compare the string value you want - and it has to be a string with a value like 91:28 - with anything else because string comparison of numbers doesn't work well. As you've see, comparing '119:54' with '11:00' is effectively comparing the third character of each string since the first two are the same, so 9 with :.

It would be simpler to leave it as a decimal fraction for comparison:

CASE
  WHEN round(24 * abs(RM_LIVE.TRANSACTIONLOG.TIMESTAMP
    - RM_LIVE.CRWGNDACTTIME.GNDACTSTART), 2) <= 11 THEN 'LESS"
ELSE 'MORE'
END AS "mORE/LESS",

For the 91:28 example, that will compare the decimal fraction version 91.46 instead; and for 119:54 will compare 119.9, which is more than 11; 102:41 will be compared as 102.68, which is also more than 11.

Or you could simplify it slightly by dividing the fixed value by 24 (hours in a day) instead of multiplying the time difference:

CASE
  WHEN abs(RM_LIVE.TRANSACTIONLOG.TIMESTAMP
    - RM_LIVE.CRWGNDACTTIME.GNDACTSTART) <= 11/24 THEN 'LESS"
ELSE 'MORE'
END AS "mORE/LESS",