Search code examples
sqloracleoracle-sqldeveloper

select difference of two dates in hours and minutes

i have the following problem, i am trying to find the difference of two dates in hours and minutes, 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') from dual;

obiously it returns the difference in days, so the output will be 0, i am expecting somthing like 01:15

Solution

  • 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.