To add these STRINGS you can turn them into seconds like this:
As an example I am 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
And to turn those seconds into the resulting string to store it in
bank, do so:
SELECT
TO_CHAR(TRUNC(512463/3600),'FM9900') || ':' ||
TO_CHAR(TRUNC(MOD(512463,3600)/60),'FM00') || ':' ||
TO_CHAR(MOD(512463,60),'FM00') AS MEDIA
FROM DUAL
I believe that with this you can create a function easily.
An example script for summing all 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;
In more detail, what you tried to do was the result and what you expected
– Marco Souza
I agree. You have to give more details. What do you want to do with this type of information? Do you just want to add up these figures? If that’s the case, of course you can’t work with the DATE type. It has to remain a string. If that’s the case, the only thing I remember is working the string.
– Renato Afonso
What is the expected result of this sum?
– Marconi
For guys I’m sorry, first time I use here and it’s unusual. So I need that sum to summarize data due to a business rule that I need to develop. After the sum I need to divide by another fields I will make a Count(*).
– DKABU