1

My TIME_AVG column is STRING in Oracle, I need to sum SUM (TIME_AVG) to DATE but the DATE column only accepts until 23:59:59 How to do this via query in oracle?

TIME_AVG
42:12:57
null
98:31:06
20:16:12
04:00:31
05:18:39
05:18:06
50:09:12
22:59:27

1 Answer 1

0

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
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .