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
  

Mensagemem Qui, 22 Jan 2015 9:57 am

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:
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 ?
leonchacal

Mensagemem Qui, 22 Jan 2015 12:48 pm

Você consegue isso facilmente usando funções analíticas.
Aqui tem um link muito bom:
download/funcoes_ANALITICAS.html

Veja essa função LAG:
Código: Selecionar todos
SELECT deptno, ename, hiredate,
LAG(hiredate,1,NULL)
  OVER (PARTITION BY deptno
        ORDER BY hiredate, ename) last_hire,
FROM emp
ORDER BY deptno, hiredate


Código: Selecionar todos
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!
dr_gori
Localização: Portland, OR USA

Thomas F. G

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

Mensagemem Seg, 26 Jan 2015 1:44 pm

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:

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


Código: Selecionar todos
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;

Código: Selecionar todos
select hire_date, z_timestamp_dif(hire_date,lag(hire_date) over(order by hire_date)) as gasto from employees;


Código: Selecionar todos
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
Renato Menezes Viana
Localização: Rio de Janeiro - RJ



Voltar para PL/SQL

Quem está online

Usuários navegando neste fórum: Google [Bot] e 8 visitantes