How to find difference b/w TIMESTAMP format values in Oracle?

Solution 1:

The result of timestamp arithmetic is an INTERVAL datatype. You have an INTERVAL DAY TO SECOND there...

If you want the number of minutes one way would be to use EXTRACT(), for instance:

select extract( minute from interval_difference ) * 60 
        + extract( hour from interval_difference ) * 60
        + extract( day from interval_difference ) * 60 * 24
  from ( select systimestamp - (systimestamp - 1) as interval_difference
           from dual )

Alternatively you can use a trick with dates:

select sysdate + (interval_difference * 1440) - sysdate
  from (select systimestamp - (systimestamp - 1) as interval_difference
          from dual )

The "trick" version works because of the operator order of precedence and the differences between date and timestamp arithmetic.

Initially the operation looks like this:

date + ( interval * number ) - date

As mentioned in the documentation:

Oracle evaluates expressions inside parentheses before evaluating those outside.

So, the first operation performed it to multiply the interval by 1,440. An interval, i.e. a discrete period of time, multiplied by a number is another discrete period of time, see the documentation on datetime and interval arithmetic. So, the result of this operation is an interval, leaving us with:

date + interval - date

The plus operator takes precedence over the minus here. The reason for this could be that an interval minus a date is an invalid operation, but the documentation also implies that this is the case (doesn't come out and say it). So, the first operation performed is date + interval. A date plus an interval is a date. Leaving just

date - date

As per the documentation, this results in an integer representing the number of days. However, you multiplied the original interval by 1,440, so this now represented 1,440 times the amount of days it otherwise would have. You're then left with the number of seconds.

It's worth noting that:

When interval calculations return a datetime value, the result must be an actual datetime value or the database returns an error. For example, the next two statements return errors:

The "trick" method will fail, rarely but it will still fail. As ever it's best to do it properly.

Solution 2:

SELECT (arrTime - depTime) * 1440 time_difference
  FROM Schedule
 WHERE ...

That will get you the time difference in minutes. Of course, you can do any rounding that you might need to to get whole minutes....

Solution 3:

Casting to DATE first returns the difference as a number, at least with the version of Oracle I tried.

round((cast(arrTime as date) - cast(depTime as date))*1440)

You could use TO_CHAR then convert back to a number. I have never tested the performance compared to EXTRACT, but the statement works with two dates instead of an interval which fit my needs.

Seconds:

(to_char(arrTime,'J')-to_char(depTime,'J'))*86400+(to_char(arrTime,'SSSSS')-to_char(depTime,'SSSSS'))

Minutes:

round((to_char(arrTime,'J')-to_char(depTime,'J'))*1440+(to_char(arrTime,'SSSSS')-to_char(depTime,'SSSSS'))/60)

J is julian day and SSSSS is seconds in day. Together they give an absolute time in seconds.