SUM COLUMN STRING LIKE DATE - оракул

Question

Мой столбец TIME_AVG в Oracle - STRING, мне нужно суммировать SUM (TIME_AVG) с DATE, но столбец DATE принимает только до 23:59:59. Как это сделать с помощью запроса в Oracle?

TIME_AVG
42:12:57
ноль
98:31:06
20:16:12
4:00:31
5:18:39
5:18:06
50:09:12
22:59:27

Answer 1

Вы не можете записать значение в тип данных DATE со значением часа, превышающим 24 часа. Если вы хотите сохранить продолжительность, которая может превышать один день, вы должны использовать тип данных INTERVAL .

Поскольку вы не можете изначально суммировать типы данных INTERVAL в Oracle, я бы разбил строки на их часы, минуты и секунды, суммируя общее количество секунд для каждой записи, а затем суммируя это значение для каждой записи, прежде чем представить обратно в качестве 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

Для значений, указанных в вашем вопросе, это дает 10 дней, 8 часов, 46 минут и 10 секунд:

+000000010 08:46:10.000000000

Если вы хотите полностью воспроизвести презентацию в формате чч: мм: сс из исходных данных, вы можете сделать:

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

который дает вывод:

248:46:10

Если вам действительно нужно сохранить результат в поле DATE, я полагаю, вы можете сохранить его относительно эпохи (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

который дает вывод:

1970-01-11 08:46:10