CODEWITHSUNDEEP
sqloracle-databaseplsql
Beginner
Beginner

Reputation: 109

Calculating decimal difference in minutes format

I have Time_In number(7,2) := 12.40

and Time_Out number(7,2) := 23.05

and Total_Hours number(7,2)=Time_Out-Time_In

and Result will be Total_Hours=10.65 which should be 10.25

Can someone give me SQL query to calculate difference between Time_in and Time_out.

Upvotes: 1

Views: 60

Answers (3)

David דודו Markovitz
David דודו Markovitz

Reputation: 44961

select  time_out - time_in - case when mod(time_in,1) > mod(time_out,1) then 0.4 else 0 end
from    t

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270351

Convert the hours to fractional hours:

select (trunc(time_out) + (time_out - trunc(time_out)) / 60) +
        trunc(time_in) + (time_in - trunc(time_in)) / 60)
       ) as diff_in_hours

. . .

You can convert this back to your arcane notation. Here is one method:

select trunc(diff_in_hours) + (diff_in_hours - trunc(diff_in_hours) * 60 / 100
from (select (trunc(time_out) + (time_out - trunc(time_out)) / 60) +
              trunc(time_in) + (time_in - trunc(time_in)) / 60)
             ) as diff_in_hours
      .  . .
     ) x

Upvotes: 0

user5683823
user5683823

Reputation:

Something like this should work. It's just a hack; you would do MUCH better to change the base table to store times in the DATE datatype.

The idea is, when the minutes on the time_out are less than on time_in, to subtract 1 from hours on the time_out and to add 60 minutes. This is just like subtracting 0.4 when working with decimals. So this is exactly what the solution does.

So, yes, this can be done, and it is easy to do - it is just the wrong thing to do. Good luck!

with 
     inputs ( time_in, time_out ) as (
       select  9.45, 12.06 from dual union all
       select 10.20, 14.02 from dual
     )
select time_in, time_out, 
       case when time_out - trunc(time_out) >= time_in - trunc(time_in)
                   then time_out - time_in
            else time_out - time_in - 0.4 end   as difference
from inputs;


TIME_IN  TIME_OUT  DIFFERENCE
   9.45     12.06        2.21
   10.2     14.32        4.12

Upvotes: 1

Related Questions