SOMAR STRING as DATE - Oracle

1

I have the TEMPO column with type STRING and I need to add the values as hours. I use all sorts of queries to do this sum, but I always fall into the field problem with DATATYPE DATE do not accept values larger than 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  
null  
25:39:39  
25:20:27  
42:12:57  
null  
98:31:06  
20:16:12  
04:00:31  
    
asked by anonymous 09.11.2016 / 05:49

1 answer

1

To sum these STRINGS you can transform them in seconds like this:

As an example I'm adding the first and second line of your question.

SELECT ( SUBSTR( '42:12:57', 1, 2 ) * 3600 )  + 
       ( SUBSTR( '42:12:57', 1, 2 ) * 60 ) + 
         SUBSTR( '42:12:57', 7, 2 ) + 
       ( SUBSTR( '98:31:06', 1, 2 ) * 3600 )  + 
       ( SUBSTR( '98:31:06', 1, 2 ) * 60 ) + 
         SUBSTR( '98:31:06', 7, 2 ) AS SEGUNDOS 
FROM DUAL

Andtotransformthesesecondsintotheresultingstringtostoreitinthebank,doso:

SELECTTO_CHAR(TRUNC(512463/3600),'FM9900')||':'||TO_CHAR(TRUNC(MOD(512463,3600)/60),'FM00')||':'||TO_CHAR(MOD(512463,60),'FM00')ASMEDIAFROMDUAL

I think with this you can easily create a function.

An example script to sum all the values in the TIME_AVG column of the table.

DECLARE
  SEGUNDOS NUMERIC( 15, 0 );
  TEMP     NUMERIC( 15, 0 );
BEGIN
 SEGUNDOS := 0;
 FOR REC IN ( SELECT TIME_AVG FROM TABELA  ) 
 LOOP
   SELECT ( SUBSTR( REC.TIME_AVG, 1, 2 ) * 3600 )  + 
          ( SUBSTR( REC.TIME_AVG, 1, 2 ) * 60 ) + 
            SUBSTR( REC.TIME_AVG, 7, 2 ) INTO TEMP  FROM DUAL;
   SEGUNDOS := SEGUNDOS + TEMP;
 END LOOP;
 .... RETORNA O VALOR 
END; 
    
09.11.2016 / 13:34