How do I get the difference in minutes from 2 timestamp columns?

Posted on

Question :

Can’t seem to figure this out. Just need the difference in minutes between 2 oracle timestamp columns.

END_TS                                  START_TS
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
11-NOV-13 PM                            11-NOV-13 PM
11-NOV-13 PM                            11-NOV-13 PM
18-NOV-13 PM                            18-NOV-13 PM
22-NOV-13 AM                            22-NOV-13 AM

Answer :

    (cast(current_timestamp as date) - cast(<other_timestamp> as date))
    * 24 * 60
  ) as diff_minutes
from <some_table>;

This is what I used to calculate the difference between the current timestamp and a heart beat table entry for latency monitoring.

Assuming your table is called TS:

SELECT (EXTRACT (DAY FROM (end_ts-start_ts))*24*60*60+
EXTRACT (HOUR FROM (end_ts-start_ts))*60*60+
EXTRACT (MINUTE FROM (end_ts-start_ts))*60+
EXTRACT (SECOND FROM (end_ts-start_ts)))/60

Common sense, to be honest.

Reduce everything to Seconds

  • A Minute is 60 Seconds
  • An Hour is 60 Minutes (or 60 * 60 seconds)
  • A Day is 24 Hours or (24 * 60 * 60)

We perform the difference for each of the elements, EXTRACT all of the values, convert them to the base (Second) and then scale them back up to the desired level of granularity (Minutes).

(May need an EXTRACT(YEAR too).

This seems to be a better option

select (cast(your_column1 as date) - date '1970-01-01')*24*60*60 
     - (cast(your_column2 as date) - date '1970-01-01')*24*60*60 
from your_table;

This is from postgresql, but I would hope oracle is as advanced as postgresql …

Select extract(epoch from stop_time-start_time)) ; #(in seconds)
Select extract(epoch from stop_time-start_time))/60 ; #(in minutes)

Leave a Reply

Your email address will not be published. Required fields are marked *