sql – Oracle – Best SELECT statement for getting the difference in minutes between two DateTime columns?

sql – Oracle – Best SELECT statement for getting the difference in minutes between two DateTime columns?

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 youre looking for

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:

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

sql – Oracle – Best SELECT statement for getting the difference in minutes between two DateTime columns?

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

Leave a Reply

Your email address will not be published.