SQL Oracle - 用于获取两个 DateTime 列之间的分钟差异的最佳 SELECT 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/206222/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle - Best SELECT statement for getting the difference in minutes between two DateTime columns?
提问by AJ.
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 formatsand can't seem to come up with a combination that makes sense. Is there an elegant way to do this? If not, is there anyway to do this?
我正在尝试满足来自客户的相当困难的报告请求，我需要在几分钟内找出两个 DateTime 列之间的差异。我尝试使用各种格式的trunc 和 round，但似乎无法想出一个有意义的组合。有没有一种优雅的方法来做到这一点？如果没有，有没有办法做到这一点？
回答by Justin Cave
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
应该是你要找的
回答by JosephStyons
By default, oracle date subtraction returns a result in # of days.
默认情况下，oracle 日期减法返回以天数为单位的结果。
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
回答by Cade Roux
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
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551242712657900129
