Calculando a diferença entre dois carimbos de data e hora no Oracle em milissegundos

Como faço para calcular a diferença de tempo em milissegundos entre dois timestamps no Oracle?

Quando você subtrai duas variables ​​do tipo TIMESTAMP , você obtém um INTERVAL DAY TO SECOND que inclui um número de milissegundos e / ou microssegundos, dependendo da plataforma. Se o database estiver sendo executado no Windows, o systimestamp geralmente terá milissegundos. Se o database estiver sendo executado no Unix, o systimestamp geralmente terá microssegundos.

  1 select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) 2* from dual SQL> / SYSTIMESTAMP-TO_TIMESTAMP('2012-07-23','YYYY-MM-DD') --------------------------------------------------------------------------- +000000000 14:51:04.339000000 

Você pode usar a function EXTRACT para extrair os elementos individuais de um INTERVAL DAY TO SECOND

 SQL> ed Wrote file afiedt.buf 1 select extract( day from diff ) days, 2 extract( hour from diff ) hours, 3 extract( minute from diff ) minutes, 4 extract( second from diff ) seconds 5 from (select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) diff 6* from dual) SQL> / DAYS HOURS MINUTES SECONDS ---------- ---------- ---------- ---------- 0 14 55 37.936 

Você pode então converter cada um desses componentes em milissegundos e adicioná-los

 SQL> ed Wrote file afiedt.buf 1 select extract( day from diff )*24*60*60*1000 + 2 extract( hour from diff )*60*60*1000 + 3 extract( minute from diff )*60*1000 + 4 round(extract( second from diff )*1000) total_milliseconds 5 from (select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) diff 6* from dual) SQL> / TOTAL_MILLISECONDS ------------------ 53831842 

Normalmente, no entanto, é mais útil ter a representação INTERVAL DAY TO SECOND ou ter colunas separadas para horas, minutos, segundos, etc., em vez de calcular o número total de milissegundos entre dois valores TIMESTAMP .

Aqui está um procedimento armazenado para fazer isso:

 CREATE OR REPLACE function timestamp_diff(a timestamp, b timestamp) return number is begin return extract (day from (ab))*24*60*60 + extract (hour from (ab))*60*60+ extract (minute from (ab))*60+ extract (second from (ab)); end; / 

Up Vote se você também quisesse vencer o desenvolvedor da Oracle que negou seu trabalho!

PORQUE comparar os timestamps pela primeira vez deveria levar a todos uma hora ou mais …

Solução mais fácil:

 SELECT numtodsinterval(date1-date2,'day') time_difference from dates; 

Para timestamps:

 SELECT (extract(DAY FROM time2-time1)*24*60*60)+ (extract(HOUR FROM time2-time1)*60*60)+ (extract(MINUTE FROM time2-time1)*60)+ extract(SECOND FROM time2-time1) into diff FROM dual; RETURN diff; 

Selecione date1 – (date2 – 1) * 24 * 60 * 60 * 1000 da tabela;

Melhor usar procedimentos assim:

 CREATE OR REPLACE FUNCTION timestamp_diff ( start_time_in TIMESTAMP , end_time_in TIMESTAMP ) RETURN NUMBER AS l_days NUMBER; l_hours NUMBER; l_minutes NUMBER; l_seconds NUMBER; l_milliseconds NUMBER; BEGIN SELECT extract(DAY FROM end_time_in-start_time_in) , extract(HOUR FROM end_time_in-start_time_in) , extract(MINUTE FROM end_time_in-start_time_in) , extract(SECOND FROM end_time_in-start_time_in) INTO l_days, l_hours, l_minutes, l_seconds FROM dual; l_milliseconds := l_seconds*1000 + l_minutes*60*1000 + l_hours*60*60*1000 + l_days*24*60*60*1000; RETURN l_milliseconds; END; 

Você pode verificar isso chamando:

 SELECT timestamp_diff (TO_TIMESTAMP('12.04.2017 12:00:00.00', 'DD.MM.YYYY HH24:MI:SS.FF'), TO_TIMESTAMP('12.04.2017 12:00:01.111', 'DD.MM.YYYY HH24:MI:SS.FF')) as milliseconds FROM DUAL; 

O timestamp lançado corretamente entre os formatos é uma chance de os campos serem mal interpretados.

Aqui está uma amostra de trabalho que está correta quando duas datas diferentes (Date2, Date1) são consideradas da tabela TableXYZ.

 SELECT ROUND (totalSeconds / (24 * 60 * 60), 1) TotalTimeSpendIn_DAYS, ROUND (totalSeconds / (60 * 60), 0) TotalTimeSpendIn_HOURS, ROUND (totalSeconds / 60) TotalTimeSpendIn_MINUTES, ROUND (totalSeconds) TotalTimeSpendIn_SECONDS FROM (SELECT ROUND ( EXTRACT (DAY FROM timeDiff) * 24 * 60 * 60 + EXTRACT (HOUR FROM timeDiff) * 60 * 60 + EXTRACT (MINUTE FROM timeDiff) * 60 + EXTRACT (SECOND FROM timeDiff)) totalSeconds, FROM (SELECT TO_TIMESTAMP ( TO_CHAR (Date2, 'yyyy-mm-dd HH24:mi:ss') - 'yyyy-mm-dd HH24:mi:ss'), TO_TIMESTAMP ( TO_CHAR (Date1, 'yyyy-mm-dd HH24:mi:ss'), 'yyyy-mm-dd HH24:mi:ss') timeDiff FROM TableXYZ)) 

Acima de um erro de syntax, Por favor, use o seguinte no oracle:

 SELECT ROUND (totalSeconds / (24 * 60 * 60), 1) TotalTimeSpendIn_DAYS, ROUND (totalSeconds / (60 * 60), 0) TotalTimeSpendIn_HOURS, ROUND (totalSeconds / 60) TotalTimeSpendIn_MINUTES, ROUND (totalSeconds) TotalTimeSpendIn_SECONDS FROM (SELECT ROUND ( EXTRACT (DAY FROM timeDiff) * 24 * 60 * 60 + EXTRACT (HOUR FROM timeDiff) * 60 * 60 + EXTRACT (MINUTE FROM timeDiff) * 60 + EXTRACT (SECOND FROM timeDiff)) totalSeconds FROM (SELECT TO_TIMESTAMP(TO_CHAR( date2 , 'yyyy-mm-dd HH24:mi:ss'), 'yyyy-mm-dd HH24:mi:ss') - TO_TIMESTAMP(TO_CHAR(date1, 'yyyy-mm-dd HH24:mi:ss'),'yyyy-mm-dd HH24:mi:ss') timeDiff FROM TABLENAME ) ); 

I) se você precisar calcular o tempo decorrido em segundos entre duas colunas de timestamp, tente isto:

Extrato SELECT (dia de (end_timestamp – start_timestamp)) * 86400 + extrato (hora de (end_timestamp – start_timestamp)) * 3600 + extrato (minuto de (end_timestamp – start_timestamp)) * 60 + extrato (segundo de (end_timestamp – start_timestamp)) FROM table_name

II) se vc quiser apenas mostrar a diferença horária no formato do personagem tente isto:

SELECT to_char (end_timestamp – start_timestamp) FROM nome_da_tabela