Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question

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

share|improve this 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.

share|improve this answer
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.