Sum of hour+minute oracle/sql

Asked

Viewed 6,191 times

3

Good afternoon, I have a table that stores times (hour:minute), I would need to group by date and add these intervals, for example day 01/03/2017 I have 04:35 of duration time.

Tabela

I tried a lot of different ways, but it’s not closing. At the moment I tried to use the sum "sum( to_char( to_date( VL_TEMPO_DURACAO, 'hh24:mi:ss' ), "but I can only get the sum of the hours. How could I do to sum up hour+minute and make the total per day?

Thanks in advance for any help or recommendation.

sql oracle date

  • I have this solution in Sqlserver, but I don’t know how to convert to Oracle by not having SGDB. Basically, I sum up individually the datepart hours, minutes and seconds generating an undercurrent. On the outside, I use the dateadd and we are those parts in a smalldatetime formatting it to display only the time.

  • Try to add the smallest part of the "date", seconds , convert to minutes and add the date (fraction of the day) for example 01:20 H , gives 1x60 + 20 = 80 seconds , date + ( 80 /(246060))

1 answer

1

Run SQL below to see the solution with the same example of your question:

WITH table_(data, tempo_duracao) as (
    select '01/03/2017', to_date('01/03/2017 00:45', 'DD/MM/YYYY HH24:mi') from dual union all
    select '01/03/2017', to_date('01/03/2017 02:13', 'DD/MM/YYYY HH24:mi') from dual union all
    select '01/03/2017', to_date('01/03/2017 00:10', 'DD/MM/YYYY HH24:mi') from dual union all
    select '01/03/2017', to_date('01/03/2017 01:27', 'DD/MM/YYYY HH24:mi') from dual union all
    select '02/03/2017', to_date('02/03/2017 00:23', 'DD/MM/YYYY HH24:mi') from dual union all
    select '02/03/2017', to_date('02/03/2017 06:57', 'DD/MM/YYYY HH24:mi') from dual union all
    select '02/03/2017', to_date('02/03/2017 00:05', 'DD/MM/YYYY HH24:mi') from dual union all
    select '03/03/2017', to_date('03/03/2017 03:00', 'DD/MM/YYYY HH24:mi') from dual )

select data,  numtodsinterval(sum(
    SUBSTR(to_char(tempo_duracao, 'hh24:mi:ss'), 1, 2)*3600 + 
    SUBSTR(to_char(tempo_duracao, 'hh24:mi:ss'), 4, 2)*60 + 
    SUBSTR(to_char(tempo_duracao, 'hh24:mi:ss'), 7, 2)), 'SECOND') 
from table_ group by data;

I’m taking into consideration that column duration has the format DATETIME, if it’s varchar, you may need to use the function below before using the to_char to catch only the timestamp:

TO_DATE(SuaDataAqui,'DD/MM/YYYY HH24:mi:ss')

References:

Browser other questions tagged sql oracle date

You are not signed in. Login or sign up in order to post.