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