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
Using Where Clause With Two Type of Conditions on Same Column
Sql Get Parent Where Children Have Specific Values
Sql - Sum of Positive and Negative Numbers Using Subquery
Error:More Than One Row Returned by a Subquery Used as an Expression
Copy and Insert to Same Table No Duplication and With Minor Changes to Value
Concatenate With String But Exclude When Null
Calculating Age Derived from Current Date and Dob
Sql Call Stored Procedure for Each Row Without Using a Cursor
How to Split a Comma-Separated Value to Columns
How to Parser a SQL Query to Pull Out the Column Names and Table Names
Eliminate Rows Based on Created Timestamp
How to Modify Query to Remove Double-Quotes from All Columns
What's the Best Way to Capitalise the First Letter of Each Word in a String in SQL Server
Remove Multiple Keys from Jsonb Column in One Statement
Grouping But With Keeping All Non-Null Values
Postgresql - Combining Multiple Rows With Several Attributes into One Row