Question:
How to subtrair
two TIMESTAMP
fields and receive the value in minutos
in oracle ?
DATAFIM - DATAINICIO = 2880 minutos (ou dois dias)
Answer:
Given a table T
with two fields of type TIMESTAMP
, t1
and t2
:
create table T (t1 timestamp, t2 timestamp);
We can calculate the difference in minutes by extracting and adding the different components of the interval resulting from the subtraction t2 - t1
:
select
extract(day from intervalo) * 60 * 24 + --minutos dos dias do intervalo
extract(hour from intervalo) * 60 + --minutos das horas do intervalo
extract(minute from intervalo) --minutos do intervalo
from (select t2 - t1 intervalo from T);
Explanation
Since subtracting two DATE
or TIMESTAMP
fields results in an INTERVAL
, we can then extract and add the relevant components of that interval (days, hours, minutes), ignoring the irrelevant ones (seconds and milliseconds).