1
I have the spine TEMPO like STRING and I need to add the values as hours.
I used all kinds of query to make this sum, but I always fall into the field problem with DATATYPE DATE not accept values greater 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
1
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;
Thanks for the help, @Reginaldo Rigo, and how do I divide the sum result by any number? This question is why I need to make this sum and then split a Count(*) from another column.
I gave some options on how to do this, but the application would be in your charge because you have all the information. To help you I need you to edit your question and post exactly what you need and what you have.
Browser other questions tagged oracle date
You are not signed in. Login or sign up in order to post.
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