Calcular tempo na mesma coluna

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
leonchacal
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 12
Registrado em: Seg, 18 Ago 2014 3:51 pm

Bom dia senhores,

Necessito calcular a diferença de tempo entre os registros do mesmo campo na tabela.
Possuo uma tabela de alarmes e nesta tabela está um campo de inserção (do tipo date).
Preciso saber quanto tempo de diferença existe entre uma inserção e última feita.
Por exemplo:

Selecionar tudo

Inserido em:                  Tempo
22/01/2015 08:59:00 | 03 minutos
22/01/2015 08:56:00 | 09 minutos
22/01/2015 08:47:00 | 03 minutos
22/01/2015 08:44:00 | 15 minutos
22/01/2015 08:29:00 | 
Alguém tem alguma sugestão ?
Avatar do usuário
dr_gori
Moderador
Moderador
Mensagens: 5024
Registrado em: Seg, 03 Mai 2004 3:08 pm
Localização: Portland, OR USA
Contato:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Você consegue isso facilmente usando funções analíticas.
Aqui tem um link muito bom:
https://glufke.net/oracle/download/funco ... TICAS.html

Veja essa função LAG:

Selecionar tudo

SELECT deptno, ename, hiredate,
LAG(hiredate,1,NULL)
  OVER (PARTITION BY deptno
        ORDER BY hiredate, ename) last_hire,
FROM emp
ORDER BY deptno, hiredate

Selecionar tudo

Dep Ename  Hired     LastHired 
--- ------ --------- --------- 
 10 CLARK  09-JUN-81              
    KING   17-NOV-81 09-JUN-81   
    MILLER 23-JAN-82 17-NOV-81   

 20 SMITH  17-DEC-80               
    JONES  02-APR-81 17-DEC-80   
    FORD   03-DEC-81 02-APR-81   
    SCOTT  09-DEC-82 03-DEC-81 
    ADAMS  12-JAN-83 09-DEC-82   

 30 ALLEN  20-FEB-81              
    WARD   22-FEB-81 20-FEB-81    
    BLAKE  01-MAY-81 22-FEB-81    
    TURNER 08-SEP-81 01-MAY-81   
    MARTIN 28-SEP-81 08-SEP-81     
    JAMES  03-DEC-81 28-SEP-81 
Depois que você colocar a próxima data AO LADO da original, basta substrair pra ter a diferença de tempo entre elas.
Qualquer dúvida, coloca aqui!
Renato Menezes Viana
Rank: Analista Pleno
Rank: Analista Pleno
Mensagens: 128
Registrado em: Sáb, 18 Nov 2006 11:51 am
Localização: Rio de Janeiro - RJ

Pessoal: esse negócio de calcular tempo, nada melhor que tratar sempre como TIMESTAMP e ter SEMPRE como resposta um string de 29 posições com o formato FIXO em:

Selecionar tudo

sdddddddddBHH:MM:SS.fffffffff
s = sinal
d = dias
B = branco
H = horas
: = separador entre horas, minutos e segundos
M = minutos
S = segundos
. = separador frações de segundos
f = frações de segundos

A função abaixo faz a subtração de datas que podem possuir apenas a data (dd/mm/yyyy) ou algo a mais como as horas (HH24) ficando então (dd/mm/yyyy hh) ou até vir completa (dd/mm/yyyy hh:mi:ss.ff).

Mesmo que forneça a própria coluna de uma tabela em formato DATE, o PL/SQL fará automaticamente a conversão para o formato STRING solicitado pela função.

No resultado de saída fornecido pela função, você pode dar o melhor formato que desejar.

Boa sorte,

Renato Viana

Selecionar tudo

CREATE OR REPLACE FUNCTION Z_TIMESTAMP_DIF (P_DATA_MAIOR IN STRING, P_DATA_MENOR IN STRING) RETURN VARCHAR2 AS 

v_maior timestamp;
v_menor timestamp;

v_dif varchar2(29);

BEGIN

  v_maior := to_timestamp(P_DATA_MAIOR, 'dd/mm/yyyy hh24:mi:ss.ff');
  v_menor := to_timestamp(P_DATA_MENOR, 'dd/mm/yyyy hh24:mi:ss.ff');
  
  v_dif := v_maior - v_menor;
  
  RETURN v_dif;
  
END Z_TIMESTAMP_DIF;
--------------------------------------------------------

Exemplo do uso em em uma tabela com coluna no formato DATE;

Selecionar tudo

select hire_date, z_timestamp_dif(hire_date,lag(hire_date) over(order by hire_date)) as gasto from employees;

Selecionar tudo

HIRE_DATE GASTO
13/01/01	
07/06/02	+000000510 00:00:00.000000000
07/06/02	+000000000 00:00:00.000000000
07/06/02	+000000000 00:00:00.000000000
07/06/02	+000000000 00:00:00.000000000
16/08/02	+000000070 00:00:00.000000000
17/08/02	+000000001 00:00:00.000000000
07/12/02	+000000112 00:00:00.000000000
01/05/03	+000000145 00:00:00.000000000
18/05/03	+000000017 00:00:00.000000000
Responder
  • Informação