oracle 在Oracle中，是否有计算两个日期之间差异的函数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/952149/
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
In Oracle, is there a function that calculates the difference between two Dates?
提问by Tony
In Oracle, is there a function that calculates the difference between two Dates? If not, is a way to display the difference between two dates in hours and minutes?
在Oracle中，是否有计算两个日期之间差异的函数？如果不是，是一种以小时和分钟显示两个日期之间差异的方法吗？
Query:
询问：
SELECT Round(max((EndDate - StartDate ) * 24), 2) as MaximumScheduleTime,
Round(min((EndDate - StartDate) * 24), 2) as MinimumScheduleTime,
Round(avg((EndDate - StartDate) * 24), 2) as AveragegScheduleTime
FROM table1
回答by Mark Good
You can subtract two dates in Oracle. The result is a FLOAT which represents the number of days between the two dates. You can do simple arithmetic on the fractional part to calculate the hours, minutes and seconds.
您可以在 Oracle 中减去两个日期。结果是一个 FLOAT，表示两个日期之间的天数。您可以对小数部分进行简单的算术运算来计算小时、分钟和秒。
Here's an example:
下面是一个例子：
SELECT TO_DATE('2000/01/02:12:00:00PM', 'yyyy/mm/dd:hh:mi:ssam')-TO_DATE('2000/01/01:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam') DAYS FROM DUAL
Results in: 1.5
结果：1.5
回答by Michael Ellick Ang
You can use these functions :
您可以使用这些功能：
1) EXTRACT(element FROM temporal_value)
1) EXTRACT(元素来自时间值)
2) NUMTOYMINTERVAL (n, unit)
2) NUMTOYMINTERVAL (n, 单位)
3) NUMTODSINTERVAL (n, unit).
3) NUMTODSINTERVAL（n，单位）。
For example :
例如 ：
SELECT EXTRACT(DAY FROM NUMTODSINTERVAL(end_time - start_time, 'DAY'))
|| ' days ' ||
EXTRACT(HOUR FROM NUMTODSINTERVAL(end_time - start_time, 'DAY'))
||':'||
EXTRACT(MINUTE FROM NUMTODSINTERVAL(end_time - start_time, 'DAY'))
||':'||
EXTRACT(SECOND FROM NUMTODSINTERVAL(end_time - start_time, 'DAY'))
"Lead Time"
FROM table;
回答by karim79
With Oracle Dates, this is pretty trivial, you can get either TOTAL (days, hours, minutes, seconds) between 2 dates simply by subtracting them or with a little mod'ing you can get Days/Hours/Minutes/Seconds between.
使用 Oracle 日期，这非常简单，您可以通过将它们相减来获得 2 个日期之间的 TOTAL（天、小时、分钟、秒），或者稍加修改，您可以获得天/小时/分钟/秒之间的值。
http://asktom.oracle.com/tkyte/Misc/DateDiff.html
http://asktom.oracle.com/tkyte/Misc/DateDiff.html
Also, from the above link:
另外，从上面的链接：
If you really want 'datediff' in your database, you can just do something like this:
如果你真的想在你的数据库中使用 'datediff'，你可以这样做：
SQL> create or replace function datediff( p_what in varchar2,
2 p_d1 in date,
3 p_d2 in date ) return number
4 as
5 l_result number;
6 begin
7 select (p_d2-p_d1) *
8 decode( upper(p_what),
9 'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL )
10 into l_result from dual;
11
11 return l_result;
12 end;
13 /
Function created
回答by spencer7593
Q:In Oracle, is there a function that calculates the difference between two Dates?
Q：在Oracle中，有计算两个日期差的函数吗？
Just subtract one date expression from another to get the difference expressed as a number of days. The integer portion is the number of whole days, the fractional portion is the fraction of a day. Simple arithmetic after that, multiply by 24 to get hours.
只需从另一个日期表达式中减去一个日期表达式即可得到表示为天数的差异。整数部分是全天数，小数部分是一天的分数。之后的简单算术，乘以 24 得到小时。
Q:If not, is a way to display the difference between two dates in hours and minutes?
问：如果不是，有没有办法以小时和分钟显示两个日期之间的差异？
It's just a matter of expressing the duration as whole hours and remainder minutes.
这只是将持续时间表示为整小时和剩余分钟的问题。
We can go "old school" to get durations in hhhh:mi format using a combination of simple builtin functions:
我们可以“老派”使用简单的内置函数组合以 hhhh:mi 格式获取持续时间：
SELECT decode(sign(t.maxst),-1,'-','')||to_char(floor(abs(t.maxst)/60))||
decode(t.maxst,null,'',':')||to_char(mod(abs(t.maxst),60),'FM00')
as MaximumScheduleTime
, decode(sign(t.minst),-1,'-','')||to_char(floor(abs(t.minst)/60))||
decode(t.minst,null,'',':')||to_char(mod(abs(t.minst),60),'FM00')
as MinimumScheduleTime
, decode(sign(t.avgst),-1,'-','')||to_char(floor(abs(t.avgst)/60))
decode(t.avgst,null,'',':')||to_char(mod(abs(t.avgst),60),'FM00')
as AverageScheduleTime
FROM (
SELECT round(max((EndDate - StartDate) *1440),0) as maxst
, round(min((EndDate - StartDate) *1440),0) as minst
, round(avg((EndDate - StartDate) *1440),0) as avgst
FROM table1
) t
Yeah, it's fugly, but it's pretty fast. Here's a simpler case, that shows better what's going on:
是的，它很笨拙，但速度非常快。这是一个更简单的案例，它更好地显示了正在发生的事情：
select dur as "minutes"
, abs(dur) as "unsigned_minutes"
, floor(abs(dur)/60) as "unsigned_whole_hours"
, to_char(floor(abs(dur)/60)) as "hhhh"
, mod(abs(dur),60) as "unsigned_remainder_minutes"
, to_char(mod(abs(dur),60),'FM00') as "mi"
, decode(sign(dur),-1,'-','') as "leading_sign"
, decode(dur,null,'',':') as "colon_separator"
from (select round(( date_expr1 - date_expr2 )*24*60,0) as dur
from ...
)
(replace date_expr1and date_expr2with date expressions)
（用日期表达式替换date_expr1和date_expr2）
let's unpack this
让我们解开这个
date_expr1 - date_expr2returns difference in number of days- multiply by 1440 (24*60) to get duration in minutes
round(orfloor) to resolve fractional minutes into integer minutes- divide by 60, integer quotient is hours, remainder is minutes
absfunction to get absolute value (change negative values to positive)to_charformat modelFM00give two digits (leading zeros)- use
decodefunction to format a negative sign and a colon (if needed)
date_expr1 - date_expr2返回天数差异- 乘以 1440 (24*60) 得到持续时间（以分钟为单位）
round（或floor）将小数分钟解析为整数分钟- 除以 60，整数商为小时，余数为分钟
abs获取绝对值的函数（将负值更改为正值）to_char格式模型FM00给出两位数（前导零）- 使用
decode函数格式化负号和冒号（如果需要）
The SQL statement could be made less ugly using a PL/SQL function, one that takes two DATE argumentsa duration in (fractional) days and returns formatted hhhh:mi
使用 PL/SQL 函数可以使 SQL 语句变得不那么难看，它需要 两个 DATE 参数以（小数）天为单位的持续时间并返回格式化为 hhhh:mi
(untested)
（未经测试）
create function hhhhmi(an_dur in number)
return varchar2 deterministic
is
begin
if an_dur is null then
return null;
end if;
return decode(sign(an_dur),-1,'-','')
|| to_char(floor(abs(an_dur)*24))
||':'||to_char(mod((abs(an_dur)*1440),60),'FM00');
end;
With the function defined:
定义了函数：
SELECT hhhhmi(max(EndDate - StartDate)) as MaximumScheduleTime
, hhhhmi(min(EndDate - StartDate)) as MinimumScheduleTime
, hhhhmi(avg(EndDate - StartDate)) as AverageScheduleTime
FROM table1
回答by RetroCoder
You can use the months_between function to convert dates to the difference in years and then use between the decimal years you are interested:
您可以使用months_between 函数将日期转换为以年为单位的差异，然后在您感兴趣的十进制年份之间使用：
CASE
WHEN ( ( MONTHS_BETWEEN( TO_DATE(date1, 'YYYYMMDD'),
TO_DATE(date1,'YYYYMMDD'))/12
)
BETWEEN Age1DecimalInYears AND Age2DecimalInYears
)
THEN 'It is between the two dates'
ELSE 'It is not between the two dates'
END;
You may need to change date format to match the a given date format and verify that 31 day months work for your specific scenarios.
您可能需要更改日期格式以匹配给定的日期格式，并验证 31 天月份是否适用于您的特定场景。
References:
( found on www on 05/15/2015 )
1. Oracle/PLSQL: MONTHS_BETWEEN Function
2. Oracle Help Center - MONTHS_BETWEEN
参考资料：（在 www 上 05/15/2015 上找到）
1. Oracle/PLSQL： MONTHS_BETWEEN 函数
2. Oracle 帮助中心 - MONTHS_BETWEEN
