Subtract two TIMESTAMP and get the value in minutes in Oracle

Question:

How to subtrair two TIMESTAMP fields and receive the value in minutos in ?

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).

Scroll to Top