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

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 01.52.14.000000 PM                            11-NOV-13 01.51.14.000000 PM
11-NOV-13 02.20.47.000000 PM                            11-NOV-13 02.19.47.000000 PM
18-NOV-13 12.44.54.000000 PM                            18-NOV-13 12.34.02.000000 PM
22-NOV-13 12.02.09.000000 AM                            22-NOV-13 12.02.08.000000 AM
``````
-

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
FROM TS;
``````

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).

-
``````select
round(
(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.

-
Why this one has been downvoted ? The query doesn't look too bad in my mind and is easier to read than the other answer... – Guillaume Nov 6 '14 at 14:06