You cannot write a value to the DATE
datatype with an hour value greater than 24 hours. If you wish to store durations which could exceed one day, you should use an INTERVAL
data type.
As you cannot natively sum INTERVAL
datatypes in Oracle, I would break down the strings into their hours, minutes and seconds, summing the total seconds for each record and then summing this value for each record before presenting back as an INTERVAL
:
SELECT
NUMTODSINTERVAL(SUM((TO_NUMBER(SUBSTR(TIME_AVG,1,2))*3600)+(TO_NUMBER(SUBSTR(TIME_AVG,4,2))*60)+TO_NUMBER(SUBSTR(TIME_AVG,7,2))), 'second')
FROM table
WHERE TIME_AVG IS NOT NULL
For the values given in your question, this results in 10 Days, 8 Hours, 46 Minutes and 10 Seconds:
+000000010 08:46:10.000000000
If you want to absolutely reproduce the hh:mm:ss presentation from the original data, you could do:
SELECT
TO_CHAR(FLOOR(SUM((TO_NUMBER(SUBSTR(TIME_AVG,1,2))*3600)+(TO_NUMBER(SUBSTR(TIME_AVG,4,2))*60)+TO_NUMBER(SUBSTR(TIME_AVG,7,2)))/3600)) || ':' ||
TO_CHAR(FLOOR(MOD(SUM((TO_NUMBER(SUBSTR(TIME_AVG,1,2))*3600)+(TO_NUMBER(SUBSTR(TIME_AVG,4,2))*60)+TO_NUMBER(SUBSTR(TIME_AVG,7,2))),3600)/60)) || ':' ||
TO_CHAR(MOD(SUM((TO_NUMBER(SUBSTR(TIME_AVG,1,2))*3600)+(TO_NUMBER(SUBSTR(TIME_AVG,4,2))*60)+TO_NUMBER(SUBSTR(TIME_AVG,7,2))),60))
FROM table
WHERE TIME_AVG IS NOT NULL
which gives the output:
248:46:10
If you really, really, really have to store the result in a DATE field, I suppose you could store it relative to the Epoch (1970-01-01):
SELECT
TO_CHAR(TO_DATE('1-1-1970 00:00:00','DD-MM-YYYY HH24:Mi:SS') + (SUM((TO_NUMBER(SUBSTR(TIME_AVG,1,2))*3600)+(TO_NUMBER(SUBSTR(TIME_AVG,4,2))*60)+TO_NUMBER(SUBSTR(TIME_AVG,7,2)))/86400), 'YYYY-MM-DD HH24:Mi:SS')
FROM table
WHERE TIME_AVG IS NOT NULL
which gives the output:
1970-01-11 08:46:10