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