CODEWITHSUNDEEP
sqloracle-databasedatesumtimestamp
user3187960
user3187960

Reputation: 337

Sql Date to Hours (total)

I have this query:

   SELECT
     NUMTODSINTERVAL(
     SUM( TO_DATE( MT.TI_CONTR, 'HH24:MI' ) - TO_DATE( '00:00', 'HH24:MI' ) ),
     'DAY'
     ) AS total
   FROM MYTABLE MT;

Executing this query i get the following:

+22 19:02:00.000000
+94 19:26:00.000000
+46 03:50:00.000000
+76 08:30:00.000000
+44 02:42:00.000000

which is ofcourse grouping in days the hours once the reach the 24.

The column TI_CONTR is a varchar storing hours and minutes in this format : hh:mm (ex. '05:22').

How can i get the resault as a total amount of hours (ex 252:20)?

Thanks

Upvotes: 0

Views: 56

Answers (1)

Michael Broughton
Michael Broughton

Reputation: 4055

Oracle does not allow you to do a SUM() on INTERVAL datatypes, so this is probably best solved with good old-fashioned SUBSTR() and math.

with dat as (SELECT '19:02' t1_contr from dual 
             union all
             SELECT '19:26' t1_contr from dual 
             union all
             SELECT '03:50' t1_contr from dual 
             union all
             SELECT '08:30' t1_contr from dual 
             union all
             SELECT '02:42' t1_contr from dual 
             )
select to_char(sum(substr(t1_contr,1,2)) --sum the hours, then add 
             + trunc(sum(substr(t1_contr,4,2))/60)) --the hours portion of the summed minutes
             ||':'|| -- put in your separator
             to_char( mod(sum(substr(t1_contr,4,2)),60)) --and append the summed minutes after removing the hours
from dat

Upvotes: 2

Related Questions