How to subtrair
two fields TIMESTAMP
and receive the value in minutos
in oracle ?
DATAFIM - DATAINICIO = 2880 minutos (ou dois dias)
How to subtrair
two fields TIMESTAMP
and receive the value in minutos
in oracle ?
DATAFIM - DATAINICIO = 2880 minutos (ou dois dias)
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);
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).
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.
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
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;