Como sumarizar períodos?

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 93
Registrado em: Ter, 06 Abr 2010 11:31 am
Localização: São Paulo - SP

Ola senhores,

Tenho em uma query o calculo entre datas, onde pego período inicial menos o final e acho a diferença, no geral retorna minutos....mas posso ter horas tb....

Selecionar tudo

To_Char(trunc(((86400*(dt_fim-dt_inicio))/60)/60)-24*(trunc((((86400*(dt_fim-dt_inicio))/60)/60)/24)),'09')
    || ':' || To_Char(trunc((86400*(dt_fim-dt_inicio))/60)-60*(trunc(((86400*(dt_fim-dt_inicio))/60)/60)),'09')

agora preciso sumarizar este resultado, ou seja, tenho que somar o total do tempo decorrido. A principio tentei colocar um sum mas não rola, pois o retorno da sintaxe acima é to_char....

Ex. se tenho o retorno de 2 linhas com 00:15:15 e 00:00:05, total deve ficar 00:15:20...

Alguém sabe como fazer?
Obrigado
RafaelFantin
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 9
Registrado em: Qui, 15 Dez 2011 4:01 pm

Acho que a melhor maneira, seria criar uma função que retorne a soma do tempo.
DanielNN
Moderador
Moderador
Mensagens: 641
Registrado em: Seg, 03 Set 2007 3:26 pm
Localização: Fortaleza - CE
att,

Daniel N.N.

Função útil para este caso.

Selecionar tudo

CREATE OR REPLACE FUNCTION time_diff (
DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS

NDATE_1   NUMBER;
NDATE_2   NUMBER;
NSECOND_1 NUMBER(5,0);
NSECOND_2 NUMBER(5,0);

BEGIN
  -- Get Julian date number from first date (DATE_1)
  NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

  -- Get Julian date number from second date (DATE_2)
  NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

  -- Get seconds since midnight from first date (DATE_1)
  NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

  -- Get seconds since midnight from second date (DATE_2)
  NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

  RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
Fonte: http://psoug.org/reference/date_func.html
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 93
Registrado em: Ter, 06 Abr 2010 11:31 am
Localização: São Paulo - SP

Vamos ao exemplo com uma linha....

Selecionar tudo

Dt Inicial = 12/01/2012 09:08:38
Dt Final = 	12/01/2012 09:30:39
Diferença = 00:22

Selecionar tudo

Dt Final - Dt Inicial = 0,0152893518518518518518518518518518518519
Apos passar as datas pela função recebo o retorno = 1321

Como converter este retorno para que fique igual ao 00:22?

valeu.....
DanielNN
Moderador
Moderador
Mensagens: 641
Registrado em: Seg, 03 Set 2007 3:26 pm
Localização: Fortaleza - CE
att,

Daniel N.N.

A conversão do número para os valores de data:

Selecionar tudo

    dias    := trunc(res / 86400, 0);
    horas   := trunc(MOD(res, 86400) / 3600, 0) + (dias * 24);
    minutos := trunc(MOD(res, 3600) / 60);
    seg     := MOD(res, 60);
Depois tu concatena aí pra sair no formato "bonitim" com os ":".
Maikew
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 29
Registrado em: Qua, 18 Jan 2012 8:55 am

Noctifero escreveu:A conversão do número para os valores de data:

Selecionar tudo

    dias    := trunc(res / 86400, 0);
    horas   := trunc(MOD(res, 86400) / 3600, 0) + (dias * 24);
    minutos := trunc(MOD(res, 3600) / 60);
    seg     := MOD(res, 60);
Depois tu concatena aí pra sair no formato "bonitim" com os ":".
Olá pessoal, eu também tava passando pelo mesmo problema no calculo de horas, utilizei esta função e adaptei pra mim, só que, eu não estou conseguindo fazer com que a cada 24 hrs completas ele some mais um dia, e a cada 60m mais uma hora. Eu até desconsiderei os segundos, pois o importante pra mim são os dias, horas e os minutos. alguém pode me ajudar com isso?

Obrigado.
Editado pela última vez por Maikew em Qui, 19 Jan 2012 9:38 am, em um total de 1 vez.
Maikew
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 29
Registrado em: Qua, 18 Jan 2012 8:55 am

Segue aí a função que eu adaptei pra retornar os dados concatenados:

Selecionar tudo

CREATE OR REPLACE FUNCTION time_diff -- Função que retorna o tempo de duração da tarefa
(
  p_dt_inicio IN DATE
 ,p_dt_fim    IN DATE
) RETURN VARCHAR2 IS
  v_ndate_ini   NUMBER;
  v_ndate_fim   NUMBER;
  v_nsecond_ini NUMBER(5, 0);
  v_nsecond_fim NUMBER(5, 0);
  v_num_conv    NUMBER;
  v_dias        NUMBER;
  v_horas       NUMBER;
  v_minutos     NUMBER;
  v_result      VARCHAR2(50);
BEGIN
  BEGIN
    
    v_ndate_ini := to_number(to_char(p_dt_inicio, 'J'));
    
    v_ndate_fim := to_number(to_char(p_dt_fim, 'J'));
   
    v_nsecond_ini := to_number(to_char(p_dt_inicio, 'SSSSS'));
   
    v_nsecond_fim := to_number(to_char(p_dt_fim, 'SSSSS'));
    v_num_conv    := (((v_ndate_fim - v_ndate_ini) * 86400) +
                     (v_nsecond_fim - v_nsecond_ini));
    v_dias        := trunc(v_num_conv / 86400, 0);
    v_horas       := trunc(MOD(v_num_conv, 86400) / 3600, 0) +
                     (v_dias * 24);
    v_minutos     := trunc(MOD(v_num_conv, 3600) / 60);
    
    v_result      := trim(to_char(v_dias))|| 'd(s), ' ||trim(to_char(v_horas, '09')) || ':' ||
                     trim(to_char(v_minutos, '09')); 
  END;
  RETURN v_result;
END;
Só falta somar mais um dia a cada 24 hrs daí... Me ajudem!
DanielNN
Moderador
Moderador
Mensagens: 641
Registrado em: Seg, 03 Set 2007 3:26 pm
Localização: Fortaleza - CE
att,

Daniel N.N.

Selecionar tudo

DECLARE
  --Parametros da data
  date_1 DATE := TO_DATE('16/01/2012 10:30:14', 'dd/mm/rrrr hh24:mi:ss');
  date_2 DATE := SYSDATE;
  ---  
  ndate_1   NUMBER;
  ndate_2   NUMBER;
  nsecond_1 NUMBER(5, 0);
  nsecond_2 NUMBER(5, 0);
  ---
  res     NUMBER;
  dias    NUMBER;
  horas   NUMBER;
  minutos NUMBER;
  seg     NUMBER;
BEGIN
  ndate_1   := to_number(to_char(date_1, 'J'));
  ndate_2   := to_number(to_char(date_2, 'J'));
  nsecond_1 := to_number(to_char(date_1, 'SSSSS'));
  nsecond_2 := to_number(to_char(date_2, 'SSSSS'));
  --total de horas.
  res       := ((ndate_2 - ndate_1) * 86400) + (nsecond_2 - nsecond_1);

  dias    := trunc(res / 86400, 0);
  horas   := trunc(MOD(res, 86400) / 3600, 0); /* + (dias * 24)*/
  minutos := trunc(MOD(res, 3600) / 60);
  seg     := MOD(res, 60);
  dbms_output.put_line('EM DIAS : ' || dias || ' dias ' || horas || ':' || minutos || ':' || seg);
  ---
  horas := horas + (dias * 24);
  dbms_output.put_line('EM HORAS: ' || horas || ':' || minutos || ':' || seg);
END;
Bem é a mesma coisa que tinha postado mas junto.

O retorno (variavel "res") deve ser em numero e somar se preciso e depois formatar como fiz.
Maikew
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 29
Registrado em: Qua, 18 Jan 2012 8:55 am

Muito obrigado, as vezes é tão simples mas o cérebro da gente trava de uma maneira kkkk

valeu msmo!!
Agraço :lol:
xprata
Rank: Analista Júnior
Rank: Analista Júnior
Mensagens: 93
Registrado em: Ter, 06 Abr 2010 11:31 am
Localização: São Paulo - SP

Obrigado meu camarada.....!!!!!
Maikew
Rank: Programador Júnior
Rank: Programador Júnior
Mensagens: 29
Registrado em: Qua, 18 Jan 2012 8:55 am

Noctifero escreveu:

Selecionar tudo

DECLARE
  --Parametros da data
  date_1 DATE := TO_DATE('16/01/2012 10:30:14', 'dd/mm/rrrr hh24:mi:ss');
  date_2 DATE := SYSDATE;
  ---  
  ndate_1   NUMBER;
  ndate_2   NUMBER;
  nsecond_1 NUMBER(5, 0);
  nsecond_2 NUMBER(5, 0);
  ---
  res     NUMBER;
  dias    NUMBER;
  horas   NUMBER;
  minutos NUMBER;
  seg     NUMBER;
BEGIN
  ndate_1   := to_number(to_char(date_1, 'J'));
  ndate_2   := to_number(to_char(date_2, 'J'));
  nsecond_1 := to_number(to_char(date_1, 'SSSSS'));
  nsecond_2 := to_number(to_char(date_2, 'SSSSS'));
  --total de horas.
  res       := ((ndate_2 - ndate_1) * 86400) + (nsecond_2 - nsecond_1);

  dias    := trunc(res / 86400, 0);
  horas   := trunc(MOD(res, 86400) / 3600, 0); /* + (dias * 24)*/
  minutos := trunc(MOD(res, 3600) / 60);
  seg     := MOD(res, 60);
  dbms_output.put_line('EM DIAS : ' || dias || ' dias ' || horas || ':' || minutos || ':' || seg);
  ---
  horas := horas + (dias * 24);
  dbms_output.put_line('EM HORAS: ' || horas || ':' || minutos || ':' || seg);
END;
Bem é a mesma coisa que tinha postado mas junto.

O retorno (variavel "res") deve ser em numero e somar se preciso e depois formatar como fiz.
Se interessar, para considerar que cada dia de trabalho é equivalente à 8hrs, no caso de usar esse periodo de duração como para uma tarefa do trabalho por exemplo, basta substituir o numero 86400 na divisão (que é equivalente ao numero de segundos dentro de 24 hrs) por 28800 que equivalente à 8 hrs.

Abraço
Diego_Mello
Rank: DBA Júnior
Rank: DBA Júnior
Mensagens: 229
Registrado em: Sex, 05 Set 2008 2:59 pm
Localização: Igrejinha - RS
Diego Mello
Igrejinha - RS
www.twitter.com/diegolmello

xprata escreveu:Vamos ao exemplo com uma linha....

Dt Inicial = 12/01/2012 09:08:38
Dt Final = 12/01/2012 09:30:39

Diferença = 00:22

Dt Final - Dt Inicial = 0,0152893518518518518518518518518518518519

Apos passar as datas pela função recebo o retorno = 1321

Como converter este retorno para que fique igual ao 00:22?

valeu.....
Buenas!
O tópico já foi solucionado, mas vamos dar mais algumas dicas...

Para transformar o retorno acima em data, basta somá-lo como abaixo.

Selecionar tudo

SQL> SELECT TRUNC(SYSDATE, 'year') + 0.0152893518518518518518518518518518518519
  2    FROM dual;
 
TRUNC(SYSDATE,'YEAR')+0.015289
------------------------------
01/01/2012 00:22:01
Há uma forma simples de trabalhar com datas...
Por exemplo:
A) Se quisermos somar dias, simplesmente + 1.
B) Se quisermos somar horas, + 1 / 24.
C) Minutos, + 1 / 60 / 24.
D) Para saber a diferença entre as datas, basta subtraí-las e somar com o primeiro dia do ano (idem primeira query).

Dessa forma, fica muito mais fácil trabalhar com queries rápidas do dia-a-dia.

Espero que tenha ajudado.
Qualquer dúvida, posta aí! :P
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 8 visitantes