Oracle SQL Hours Difference Between Dates in Hh:Mm:Ss

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.

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;

Subtracting two dates to get hh:mm:ss

Assuming that mopend and mopstart are both date columns, subtracting two dates return a difference in days. If you want to format that into hours, minutes, and seconds, you'll need to do a bit of math.

with diffs as (
select 0.125 diff_in_days from dual
)
select trunc( mod(diff_in_days * 24, 24) ) diff_in_hours,
trunc( mod(diff_in_days * 24 * 60, 60) ) diff_in_mins,
trunc( mod(diff_in_days * 24 * 60 * 60, 60) ) diff_in_secs
from diffs;

An alternative would be to use an interval and extract the individual components

with diffs as (
select numtodsinterval( 0.125, 'day' ) diff_interval from dual
)
select extract( hour from diff_interval ) diff_in_hours,
extract( minute from diff_interval ) diff_in_mins,
extract( second from diff_interval ) diff_in_secs
from diffs;

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 exact hours between two datetime fields?

The 'format' comment on your first query suggests your columns are timestamps, despite the dummy column names, as the result of subtracting two timestamps is an interval. Your second query is implicitly converting both timestamps to dates before subtracting them to get an answer as a number of days - which would be fractional if you weren't truncating them and thus losing the time portion.

You can extract the number of hours from the interval difference, and also 24 * the number of days if you expect it to exceed a day:

extract(day from (date1 - date2)) * 24 + extract(hour from (date1 - date2))

If you want to include fractional hours then you can extract and manipulate the minutes and seconds too.

You can also explicitly convert to dates, and truncate or floor after manipulation:

floor((cast(date1 as date) - cast(date2 as date)) * 24)

db<>fiddle demo



Related Topics



Leave a reply



Submit