using sum() for adding all of the values with the time type

user_11617688 0
Viewed 45

I have a column in a table that the values are in the type of time like 01:12 and 05:14 I want to use sum to add values and get the result in the same type like 06:26 I used this code to do what I want

CASE
          WHEN SUM (min) = 60
          THEN
              TO_CHAR (SUM (hour) + 1) || ':00'
          WHEN SUM (min) < 60
          THEN
                 TO_CHAR (SUM (hour))
              || ':'
              || TO_CHAR (SUM (min))
          ELSE
                 TO_CHAR (
                       SUM (hour)
                     + (  SUM (min)/60
                        -   (MOD (
                                 SUM (min),60)/60))
                          )
              || ':'
              || TO_CHAR (
                     
                         MOD (
                                 SUM (min),60))                 
      END

and it worked but it is a little complicated ... is there anything simpler than what I did...please tell me... thank you

sum datetime oracle sql string
1 Answers

You can use arithmetics, then string functions.

If you the total hours and minutes in separate columns:

floor(sum(hour * 60 + min) / 60 total_hours,
mod(sum(hour * 60 + min), 60) total_minutes

If you want the result formated as HH24:MI:

lpad(floor(sum(hour * 60 + min) / 60), 2, '0') 
    || ':'
    || lpad(mod(sum(hour * 60 + min), 60), 2, '0') total_time

If you are starting from a string in format `HH24:MI' (as opposed to what is showned in your query):

lpad(floor(sum(substr(col, 1, 2) * 60 + substr(col, -2)) / 60), 2, '0') 
    || ':'
    || lpad(mod(sum(substr(col, 1, 2) * 60 + substr(col, -2)), 60), 2, '0') total_time

Demo on DB Fiddle:

with t as (
    select 1 hour, 12 min from dual 
    union all select 6 hour, 26 min from dual
)
select lpad(floor(sum(hour * 60 + min) / 60), 2, '0') 
    || ':'
    || lpad(mod(sum(hour * 60 + min), 60), 2, '0') total_time
from t

| TOTAL_TIME |
| :--------- |
| 07:38      |


with t as (
    select '01:12' col from dual
    union all select '06:26' from dual
)
select lpad(floor(sum(substr(col, 1, 2) * 60 + substr(col, -2)) / 60), 2, '0') 
    || ':'
    || lpad(mod(sum(substr(col, 1, 2) * 60 + substr(col, -2)), 60), 2, '0') total_time
from t

| TOTAL_TIME |
| :--------- |
| 07:38      |
user_106767160
Related