Subtract two TIMESTAMP and receive the value in minutes on Oracle

Asked

Viewed 22,316 times

6

Like subtrair two fields TIMESTAMP and receive the value in minutos in the ?

DATAFIM - DATAINICIO = 2880 minutos (ou dois dias)
oracle oracle10g

4 answers

6


Given a table T with two fields of the type TIMESTAMP, t1 and t2:

create table T (t1 timestamp, t2 timestamp);

We can calculate the difference in minutes by extracting and summing the different components of the interval resulting from 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

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

  • Strange I applied your idea, but the calculation went wrong. The calculation of DATAFIM (23/04/14 08:21:32,819000000) - DATAINICIO (23/04/14 08:13:55,261000000) resulted 16 when should return 8.

  • Your solution worked perfectly. The mistake was mine here.

  • The solution: SELECT Extract(day from 2460(DATAFIM - DATAINICIO)) THE INTERVAL FROM my_table met me, but I will give your answer as certain.

  • @Geisonsantos You are free to give the answer you prefer as sure. Do not hold back for who answered or the order of the answers. ;)

2

The answer to that question I take of this post of Soen

If you multiply the interval by 24 and by 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.

2

According to this Thread from Oracle Community all dates are numbers too:

if you make Operations between Dates the result is a number

And how the rsenna indicated, its fields are TIMESTAMP which must be converted before being used in mathematical operations.

Soon we can do:

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

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

  • 1

    I tried your approach but got an error 'ORA-00932: inconsistent data types: expected NUMBER got TIMESTAMP'

  • 2

    @Spark @Geisonsantos In fact all kinds fields DATE are numbers too. But fields of type TIMESTAMP are "objects", cannot be subtracted so easily, and a cast is required first. Using the Spark idea, the following query would work: SELECT (CAST(T1 AS DATE) - CAST(T2 AS DATE)) * 1440 FROM T.

  • 1

    Follows a fiddle to prove that subtraction works.

  • 1

    @rsenna, thanks for the clarification. Very good your tip about sqlfiddle; ignorance.

  • opa @rsenna I will fix, thanks

1

Through the answers given by my colleagues I have arrived at my own answer. It is not the best or most elaborate, but rather a synthesis, a simplification and that has been posted to the knowledge of the adopted solution.

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;

Browser other questions tagged oracle oracle10g

You are not signed in. Login or sign up in order to post.