oracle 如何获得两个日期之间的小时差？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9488475/
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
How can I get the difference in hours between two dates?
提问by Guilherme Costa
I have to do a "select" that returns the difference between a field of type date from the database and the current date in hours. How can I do this in oracle?
我必须做一个“选择”，返回数据库中日期类型的字段与当前日期之间的差异（以小时为单位）。我怎么能在oracle中做到这一点？
I try this:
我试试这个：
select 24 * (to_date(sysdate, 'YYYY-MM-DD hh24:mi')
- to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) diff_hours
from dual;
But that did not work.
但这没有用。
回答by aweis
The error is because SYSDATE is already a date, there's no need to use TO_DATE()to convert it to a date.
错误是因为 SYSDATE 已经是一个日期，没有必要使用TO_DATE()将其转换为日期。
If you don't convert it to a date:
如果您不将其转换为日期：
select
24 * (sysdate - to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) as diff_hours
from dual;
And if the formatting of the dates are wrong, you can possible use two steps like:
如果日期格式错误，您可以使用以下两个步骤：
select
24 * (to_date(to_char(sysdate, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) as diff_hours
from dual;
回答by Geysa O. Marinho
Diference between two dates in hours and minutes
以小时和分钟为单位的两个日期之间的差异
SELECT TRUNC(minutes_ / 60) || ' Hours, ' || TRUNC(MOD(minutes_, 60)) || ' Minutes' diference
FROM ( SELECT (sysdate - to_date('16/10/2019 18:45:00', 'DD/MM/YYYY HH24:MI:SS')) * 24 * 60 AS minutes_
FROM dual
);
Diference between two dates in hours
两个日期之间的时差（以小时为单位）
SELECT ROUND(minutes_ / 60, 2) || ' Hours '
FROM ( SELECT (sysdate - to_date('16/10/2019 18:45:00', 'DD/MM/YYYY HH24:MI:SS')) * 24 * 60 AS minutes_
FROM dual
);
Diference between two dates in hours (truncate)
以小时为单位的两个日期之间的差异（截断）
SELECT ROUND(minutes_ / 60, 2) || ' Hours '
FROM ( SELECT (sysdate - to_date('16/10/2019 18:45:00', 'DD/MM/YYYY HH24:MI:SS')) * 24 * 60 AS minutes_
FROM dual
);
回答by ibrahim
I did this way:
我是这样做的：
select to_number(to_char(sysdate,'HH24')) - to_number(to_char(*YOUR_DATA_VALUE*,'HH24')),max(exp_time) from ...
Also, you can check this for minutes : Oracle : how to subtract two dates and get minutes of the result
此外，您可以检查此分钟：Oracle：如何减去两个日期并获得结果的分钟数
回答by Amir Pashazadeh
select trunc((sysdate - to_date('2012-02-28 15:20', 'YYYY-MM-DD hh24:mi')) / 24)
from dual;
