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

Question

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 to use trunc and round with various formats and can't seem to come up with a combination that makes sense. Is there an elegant way to do this? If not, is there any way to do this?

Imide

Answer 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

Passible

I didn't know date subtraction returned the number of days. Now I know. – Nitrometer

Don't forget to round the result! You will want ROUND((date1 - date2) * 1440,0) – Preece

@Justin Cave Will (date1 - date2) give difference_in_minutes for the same day too?? If yes why all are saying date subtraction returned the number of days ?? – Getter

@KanagaveluSugumar - No, simple subtraction returns a difference in days (which is what I said initially). You have to multiply by (24*60) to get the difference in minutes (which is also what I said initially). – Passible

@Justin Cave Sorry! I am rearranging my question. For the same day how do i get time difference in minutes? because (date1-date2) will return zero in this case? – Getter

@KanagaveluSugumar - An Oracle DATE always has a year, month, day, hour, minute, and second component. Subtracting two dates will return if and only if they are the same year, the same month, the same day, the same hour, the same minute, and the same second. If they are identical down to the second then, presumably, returning 0 is the right answer. – Passible

@Justin Cave upvoted! But still not clear how to get difference_in_minutes for the same day with two different timings? Since (date1 - date2) = 0 for the same day. Hence (date1 - date2) * 24 * 60 will be ZERO ?? i think; it will give floating point values for less than one day?? Am i correct ?? – Getter

@KanagaveluSugumar - No, you are not correct. date1 - date2 will equal 0 if an only if date1 and date2 are identical down to the second. If date1 and date2 are on the same day but have different time components, subtracting them will return a number that is greater than 0 but less than 1. Multiplying that by 24 * 60 will yield a difference in minutes. – Passible

Answer 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:

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

Milli

note that if you have timestamps instead of date you first need to cast them CAST(second_date AS DATE) - CAST(first_date AS DATE) – Sociology

Answer 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

Freshet

Both links are now gone – Vitkun

Answer 4

Use timestampdiff at where clause.

Example:

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

Balsa

No such thing in oracle sql as timestampdiff. – Ungulate