How to Subtract 2 Dates in Oracle to Get the Result in Hour and Minute

Oracle : how to subtract two dates and get minutes of the result

When you subtract two dates in Oracle, you get the number of days between the two values. So you just have to multiply to get the result in minutes instead:

SELECT (date2 - date1) * 24 * 60 AS minutesBetween
FROM ...

Oracle : how to subtract two dates and get seconds of the result

Your data type is most probably TIMESTAMP that would explain the rounding problem.

You may workaround it by first casting to DATE (to get rid of the milliseconds) and that casting it back to TIMESTAMP (to be able to perform your regexp_substr)

This sample data replays your problem

select opa.*, 
NVL(REGEXP_SUBSTR (CAST(opa.end_time AS TIMESTAMP) - CAST(opa.start_time AS TIMESTAMP), '\d{2}:\d{2}:\d{2}'),' ') AS duration,
NVL(REGEXP_SUBSTR (CAST(CAST(opa.end_time AS DATE)AS TIMESTAMP) - CAST(CAST(opa.start_time AS DATE)AS TIMESTAMP), '\d{2}:\d{2}:\d{2}'),' ') AS duration2
from tab opa;

START_TIME                           END_TIME                             DURATION                    DURATION2                   
------------------------------------ ------------------------------------ --------------------------- ---------------------------
04.05.2021 09:13:07,555000000 +02:00 04.05.2021 09:13:18,111000000 +02:00 00:00:10                    00:00:11 

How can I get the difference in hours between two dates?

The error is because SYSDATE is already a date, there's no need to use TO_DATE() to convert it to a date.

If you don't convert it to a date:

select
    24 * (sysdate - to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) as diff_hours
from dual;

And if the formatting of the dates are wrong, you can possible use two steps like:

select
    24 * (to_date(to_char(sysdate, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) as diff_hours
from dual;

How to get Time Differences of Two Dates like x days y hours z seconds t minutes

According to the datetime arithmetic matrix difference of two date values is a number, which is the number of days. As a pure number it has no time components. So it should be converted to the interval day to second with numtodsinterval function:

with a as (
  select
    sysdate as dt1,
    trunc(sysdate) as dt2
  from dual
)
select
  numtodsinterval(dt1 - dt2, 'DAY') as interval_
from a

| INTERVAL_                     |
| :---------------------------- |
| +000000000 22:38:59.000000000 |

db<>fiddle here

But if you have timestamp, then according to the same matrix the difference will be interval by default:

with a as (
  select
    sysdate as dt1,
    trunc(sysdate) as dt2
  from dual
)
select
  numtodsinterval(dt1 - dt2, 'DAY') as interval_
from a

| INTERVAL_                     |
| :---------------------------- |
| +000000000 22:38:59.000000000 |

db<>fiddle here

Note that interval has no format in to_char function, so to retrieve hours, minutes, seconds you will need to use extract function

select difference of two dates in hours and minutes

Depending on the data type you need for your result...

If an interval day to second will work, then you can do this:

select (date2 - date1) * interval '1' day from dual;

For example:

select (to_date('13.05.2021 09:30','DD.MM.YYYY HH24:MI') 
      - to_date('13.05.2021 08:15','DD.MM.YYYY HH24:MI'))
      * interval '1' day as diff
from   dual;

DIFF               
-------------------
+00 01:15:00.000000

Give this a try; if you need a different data type for the result, let us know. Note that, stupidly, Oracle doesn't support aggregate functions for intervals; so if you need a sum of such differences, you should apply the aggregation first, and only use this trick to convert to an interval as the last step.

How to calculate the difference of HH:MM:SS between two dates in oracle sql?

If you cast those dates as timestamps, you can easily subtract them and see relatively nice result:

SQL> with test (st, et) as
  2    (select to_date('27.05.2020 00:52:48', 'dd.mm.yyyy hh24:mi:ss'),
  3            to_date('27.05.2020 02:08:33', 'dd.mm.yyyy hh24:mi:ss')
  4     from dual
  5    )
  6  select cast(et as timestamp) - cast(st as timestamp) diff
  7  from test;

DIFF
--------------------------------------------------------------------------
+000000000 01:15:45.000000

SQL>

If you want to format it as you wanted (note that mm format mask is for months; mi is for minutes), then you could do some extracting - again from timestamp (won't work for date):

SQL> with test (st, et) as
  2    (select to_date('27.05.2020 00:52:48', 'dd.mm.yyyy hh24:mi:ss'),
  3            to_date('27.05.2020 02:08:33', 'dd.mm.yyyy hh24:mi:ss')
  4     from dual
  5    ),
  6  diff as
  7    (select cast(et as timestamp) - cast(st as timestamp) diff
  8     from test
  9    )
 10  select extract(hour   from diff) ||':'||
 11         extract(minute from diff) ||':'||
 12         extract(second from diff) diff
 13  from diff;

DIFF
-------------------------------------------------------------------------
1:15:45

SQL>

You can further make it pretty (e.g. two digits for hours, using LPAD function). Or, you can even write your own function which will actually work on difference of DATE datatype values, do some calculations (using trunc function, subtractions, whatnot), but the above looks pretty elegant if compared to a home-made function.

---

Related Topics