Oracle - 用于获取两个DateTime列之间的分钟差异的最佳SELECT语句？

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?

我正在尝试从客户端完成一个相当困难的报告请求，我需要找到它以在几分钟内获得两个DateTime列之间的差异。我试图使用各种格式的截断和圆形，似乎无法想出一个有意义的组合。有一种优雅的方式来做到这一点？如果没有，有没有办法做到这一点？

3 个解决方案

#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

返回天数差异。乘以24得到小时差异，24 * 60得到分钟。所以

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

should be what you're looking for

应该是你正在寻找的

#2

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

默认情况下，oracle date detraction返回＃天数的结果。

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

因此，只需乘以24即可获得小时数，再按60分钟即可获得。

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

#3

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

http://asktom.oracle.com/tkyte/Misc/DateDiff.html - 链接已截止2012-01-30

Looks like this is the resource:

看起来这是资源：

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

#1

SELECT date1 - date2
  FROM some_table