CODEWITHSUNDEEP
sqloracle-databasetimenumbers
Patta Meunmuang
Patta Meunmuang

Reputation: 21

ORACLE-SQL : how to calculate 2 time in number type?

I kept my Time data as number type (NUMBER(4,2)) and I want to calculate the column like below

2.15 (2:15 am.) - 1.45 (1:45 am)


***result***
0.30  (a half hour)

Please kindly explain me the method to calculate.

Upvotes: 0

Views: 176

Answers (6)

Alex Poole
Alex Poole

Reputation: 191570

You can convert your two 'times' to minutes; this uses bind variables to provide both numeric values as it isn't clear where you're actually getting them from:

var time_1 number;
var time_2 number;
exec :time_1 := 2.15;
exec :time_2 := 1.45;

select 60 * trunc(:time_1) + 100 * (:time_1 - trunc(:time_1)) as minutes_1,
       60 * trunc(:time_2) + 100 * (:time_2 - trunc(:time_2)) as minutes_2,
      (60 * trunc(:time_1) + 100 * (:time_1 - trunc(:time_1)))
    - (60 * trunc(:time_2) + 100 * (:time_2 - trunc(:time_2))) as minutes_diff
from dual;

 MINUTES_1  MINUTES_2 MINUTES_DIFF
---------- ---------- ------------
       135        105           30

You can then convert the difference in minutes back to a number in the (odd) format you're using by reversing the calculation; this uses a second CTE to get the difference in minutes calculated above to simplify things and avoid repeating the long terms:

with diff (minutes) as (
  select (60 * trunc(:time_1) + 100 * (:time_1 - trunc(:time_1)))
       - (60 * trunc(:time_2) + 100 * (:time_2 - trunc(:time_2)))
  from dual
)
select minutes,
       trunc(minutes/60) + mod(minutes, 60) / 100 as minutes_as_number
from diff;

   MINUTES MINUTES_AS_NUMBER
---------- -----------------
        30                .3

Upvotes: 0

Eduardo
Eduardo

Reputation: 39

DATEDIFF (Transact-SQL)

 This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

//You can return: second, minute, day, year. In your case is minute.

SELECT DATEDIFF(minute, '2018-08-03 02:15:00am', '2018-08-03 1:45:00am');

return value: -30

if you would like to get exact (30) converted in varchar use like that.

SELECT CONVERT(varchar, ABS(DATEDIFF(minute, '2018-08-03 02:15:00am', '2018-08-03 1:45:00am')));

First use ABS() to get the absolute number (removing the (-) minus signal) and convert to varchar using CONVERT().

source: https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017

ORACLE Version
SELECT TO_DATE('2000-01-02', 'YYYY-MM-DD') - TO_DATE('2000-01-01', 'YYYY-MM-DD') AS DateDiff FROM dual

Upvotes: -1

Goran Kutlaca
Goran Kutlaca

Reputation: 2024

Try this code:

select (trunc(2.15)* 0.6 + (2.15 - trunc(2.15))) - (trunc(1.45)* 0.6 + (1.45 - trunc(1.45)))
    as result
  from YOUR_TABLE

Result: 0,30

Upvotes: 1

Gowtham Pranavesh Naidu
Gowtham Pranavesh Naidu

Reputation: 11

Try this one I hove it will work for u

select to_char(to_date(((to_date(to_char(09.15),'hh24.mi')-to_date(to_char(01.45),'hh24.mi'))*24*60*60),'sssss'),'hh24:mi') time from dual;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Convert to hours:

select ( trunc(t1) + (t1 - trunc(t1)) * 60) -
         trunc(t2) + (t2 - trunc(t2)) * 60)
       ) as hours

This converts the difference to fractional hours. I would advise you to leave it like that or convert to minutes.

Upvotes: 0

JohnHC
JohnHC

Reputation: 11205

Assuming you can get them into separate columns:

with mins_calc as
(
select (floor(mytime1) - floor(mytime2))*60 + (mod(mytime1,1)-mod(mytime2,1)) as tot_mins
from Mytable
)
select to_char(floor(tot_mins/60))||'.'||to_char(mod(tot_mins,60)) as time_diff_char
from mins_calc

Upvotes: 0

Related Questions