SUM COLUMN STRING COMO DATA - oracle

Question

Minha coluna TIME_AVG é STRING no Oracle, preciso somar SUM (TIME_AVG) a DATE, mas a coluna DATE aceita apenas até 23:59:59 Como fazer isso via query no 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

    

Answer 1

Você não pode gravar um valor para o tipo de dados DATE com um valor de hora maior que 24 horas. Se você deseja armazenar durações que podem exceder um dia, use um tipo de dados INTERVAL .

Como você não pode somar INTERVAL tipos de dados no Oracle, eu dividiria as strings em horas, minutos e segundos, somando o total de segundos para cada registro e somando esse valor para cada registro antes de retornar como 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

Para os valores fornecidos na sua pergunta, isso resulta em 10 dias, 8 horas, 46 minutos e 10 segundos:

+000000010 08:46:10.000000000

Se você quiser reproduzir absolutamente a apresentação hh: mm: ss dos dados originais, você pode fazer:

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

que dá a saída:

248:46:10

Se você realmente, realmente, realmente precisa armazenar o resultado em um campo DATE, eu suponho que você poderia armazená-lo em relação à 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

que dá a saída:

1970-01-11 08:46:10