Adding Time in HH:MM Format -SQL Query

Question

Sir/Mam,

I am using Oracle 11g.I have the following table by the name "Timeduration". It contains time specified in hours and minutes.I want to get the total time in hour and minutes.

Timeduration
05:37
06:40
03:45
02:50
07:58

I want the total time as 25:30. How can i write the code for this?? Please help me out."

Answer 1

with t as (
  select '05:37' as timeduration from dual union all
  select '06:40' as timeduration from dual union all
  select '03:45' as timeduration from dual union all
  select '02:50' as timeduration from dual union all
  select '07:58' as timeduration from dual
),
d as (
  select 
      --
      --  4  Converting the sum obtained in (3) into
      --     an interval
    numtodsinterval(
      -- 
      --  3  Summing the day fractions obtained in (2)
      sum(
      --
      --  1  Convert the string 'HH:MM' to a real date.
      --
      --     For example 05:37 becomes January 1st 0001 5:37
             to_date('00010101' || t.timeduration, 'yyyymmddhh24:mi') 
      --
      --  2  Subtract January 1st 0001 from that date
      --
      --     The result is a number of a day's length
      --    [1: 24 hours, 0.3: 8 hours etc)
           - date '0001-01-01'),
      --
          'DAY'
    ) interval
  from
    t
)
select
   (
      --  5  How many days are in the interval. Each day
      --     corresponds to 24 hours:
      extract (day  from d.interval) * 24 +
      --
      --  6  The hours «below» 24 hours must be added
      --     seperatly:
      extract (hour from d.interval) 
   ) 
      --
      --  7  Hours are now extracted. Append the delimiter
     || ':' ||
   (
      --
      --  8  Finally: get the minutes from the interval
      extract (minute from d.interval)
   )
   result
from d;