Subtract two TIMESTAMP and receive the value in minutes in Oracle

6

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

DATAFIM - DATAINICIO = 2880 minutos (ou dois dias)
    
asked by anonymous 29.04.2014 / 15:10

4 answers

6

Given a T table 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 intervalor result of 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

As the subtraction of two fields DATE or TIMESTAMP results in a INTERVAL , we can then extract and sum the relevant components of that interval (days, hours, minutes), ignoring the irrelevant (seconds and milliseconds).

    
29.04.2014 / 15:40
2

The answer to this question I took from this post SOEn

If you multiply the range by 24 and 60, you get the number of minutes by extracting the number of days. It's more compact, but I'm not sure if it would be more elegant from your point of view.

SQL> create table t (meu_intervalo interval day to second)
  2  /

Table created.

SQL> insert into t
  2  select numtodsinterval(30,'minute') from dual union all
  3  select numtodsinterval(4,'hour') from dual
  4  /

2 rows created.

SQL> select meu_intervalo 
  2       , 60 * extract(hour from meu_intervalo )
  3         + extract(minute from meu_intervalo ) minutes_terrible_way
  4       , extract(day from 24*60*meu_intervalo ) minutes_other_way
  5    from t
  6  /

meu_intervalo                   MINUTES_TERRIBLE_WAY MINUTES_OTHER_WAY
------------------------------ -------------------- -----------------
+00 00:30:00.000000                              30                30
+00 04:00:00.000000                             240               240

2 rows selected.
    
29.04.2014 / 15:24
2

According to this Oracle Community Thread all dates are numbers as well:

  

If you make operations between dates, the result is a number

And as rsenna indicated, its fields are TIMESTAMP that must be converted before being used in math operations.

Soon we can do:

(CAST(DATAFIM AS DATE) * 1440) - (CAST(DATAINICIO AS DATE) * 1440)

I hate magic numbers but 1440 there is the number of minutes in a day

    
29.04.2014 / 15:42
1

Through the answers given by colleagues, I came to my own answer. It is not the best or most elaborate, but a synthesis, a simplification and was posted to the knowledge of the solution adopted.

Table structure:

CREATE TABLE T (DT_INICIO timestamp, DT_FIM timestamp);

INSERT INTO T VALUES (
  TO_DATE('2014/04/01 19:00:00', 'yyyy/mm/dd hh24:mi:ss'),
  TO_DATE('2014/04/01 19:15:00', 'yyyy/mm/dd hh24:mi:ss'));

Solution to the problem:

SELECT extract(day from 24 * 60 * (DT_FIM - DT_INICIO )) as MINUTOS FROM T;
    
29.04.2014 / 17:07