Oracle – How to Get Difference Between Two Timestamps in Minutes

oracletimestamp

Demo

create table my_table (ts1 timestamp,ts2 timestamp);

insert into my_table (ts1,ts2) values 
    (timestamp '2017-01-29 07:35:14.52',timestamp '2017-01-29 15:01:59.14');

I'm familiar with the following method:

select  (   extract (day    from (ts2-ts1))*24*60*60
        +   extract (hour   from (ts2-ts1))*60*60
        +   extract (minute from (ts2-ts1))*60
        +   extract (second from (ts2-ts1))
    )/60  as ts_diff_minutes

from    my_table
;

+------------------------------------------+
| TS_DIFF_MINUTES                          |
+------------------------------------------+
| 446.743666666666666666666666666666666667 |
+------------------------------------------+

I was wondering if there is a shorter version to achieve the same result?

Best Answer

After hours of searching over the internet, I was able to find a shorter version of the code that I was asking for. After several attempts of deriving the code I found here.

(CAST((SYSTIMESTAMP + interval '35' minute) as date) - CAST(SYSTIMESTAMP as date)) * 1440

Result: +--------+ | Result | +--------+ | 35 | +--------+

This version is faster compared to the version that uses EXTRACT. The only downside of this version is that it doesn't include fractions of a second.

Related Question