Oracle - Best Select Statement For Getting The Difference In Minutes Between Two Datetime Columns?

I'm attempting to fulfill a rather difficult reporting request from a client, and I need to find away to get the difference between two DateTime columns in minutes. I've attempted

Solution 1:

SELECT date1 - date2
  FROM some_table

returns a difference in days. Multiply by 24 to get a difference in hours and 24*60 to get minutes. So

SELECT (date1 - date2) *24*60 difference_in_minutes
  FROM some_table

should be what you're looking for

Solution 2:

By default, oracle date subtraction returns a result in # of days.

So just multiply by 24 to get # of hours, and again by 60 for # of minutes.

Example:

selectround((second_date - first_date) * (60 * 24),2) as time_in_minutes
from
  (select
    to_date('01/01/2008 01:30:00 PM','mm/dd/yyyy hh:mi:ss am') as first_date
   ,to_date('01/06/2008 01:35:00 PM','mm/dd/yyyy HH:MI:SS AM') as second_date
  from
    dual
  ) test_data

Solution 3:

http://asktom.oracle.com/tkyte/Misc/DateDiff.html - link dead as of 2012-01-30

Looks like this is the resource:

http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551242712657900129

Solution 4:

Use timestampdiff at where clause.

Example:

Select*from tavle1,table2 where timestampdiff(mi,col1,col2).