SQL 如何在oracle中减去2个日期以获得小时和分钟的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7460266/
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 to subtract 2 dates in oracle to get the result in hour and minute
提问by Macky
I want to subtract 2 dates and represent the result in hour and minute in one decimal figure.
我想减去 2 个日期，并以一位小数表示以小时和分钟为单位的结果。
I have the following table and I am doing it in this way but the result is not as desired.
我有下表，我正在这样做，但结果并不理想。
There is some slight variation, I'm sure this is simple arithmetic but I'm not getting it right.
有一些细微的变化，我确定这是简单的算术，但我没有做对。
select start_time, end_time, (end_time-start_time)*24 from
come_leav;
START_TIME END_TIME (END_TIME-START_TIME)*24 ------------------- ------------------- ------------------------ 21-06-2011 14:00:00 21-06-2011 16:55:00 2.9166667 21-06-2011 07:00:00 21-06-2011 16:50:00 9.8333333 21-06-2011 07:20:00 21-06-2011 16:30:00 9.1666667
I want the result (end_time-start_time) as below.
我想要结果 (end_time-start_time) 如下。
16:55- 14:00 = 2.55 16:50-07:00 = 9.5 16:30-7:20 = 9.1 and so on.
How can I do that?
我怎样才能做到这一点？
回答by Shannon Severance
SQL> edit
Wrote file afiedt.buf
1 select start_date
2 , end_date
3 , (24 * extract(day from (end_date - start_date) day(9) to second))
4 + extract(hour from (end_date - start_date) day(9) to second)
5 + ((1/100) * extract(minute from (end_date - start_date) day(9) to second)) as "HOUR.MINUTE"
6* from t
SQL> /
START_DATE END_DATE HOUR.MINUTE
------------------- ------------------- -----------
21-06-2011 14:00:00 21-06-2011 16:55:00 2.55
21-06-2011 07:00:00 21-06-2011 16:50:00 9.5
21-06-2011 07:20:00 21-06-2011 16:30:00 9.1
It should be noted for those coming across this code that the decimal portions are ACTUAL minute differences, and not part of an hour. .5, therefore, represents 50 minutes, not 30 minutes.
对于那些遇到此代码的人应该注意的是，小数部分是实际的分钟差异，而不是一小时的一部分。.5，因此，代表50 minutes，不是30 minutes。
回答by reader_1000
Try this
尝试这个
round(to_number(end_time - start_time) * 24)
回答by Crack
Oracle represents dates as a number of days, so (end_time-start_time)*24gives you hours. Let's assume you have this number (eg. 2.9166667) in hcolumn. Then you can easily convert it to the format you want with: FLOOR(h) + (h-FLOOR(h))/100*60.
Oracle 将日期表示为天数，因此(end_time-start_time)*24为您提供小时数。假设您2.9166667在h列中有这个数字（例如）。然后，您可以轻松地将其转换为您想要的格式：FLOOR(h) + (h-FLOOR(h))/100*60。
Example:
例子：
WITH diff AS (
SELECT (TO_DATE('21-06-2011 16:55:00', 'DD-MM-YYYY HH24:MI:SS') - TO_DATE('21-06-2011 14:00:00', 'DD-MM-YYYY HH24:MI:SS'))*24 h
FROM dual
) SELECT FLOOR(h) + (h-FLOOR(h))/100*60
FROM diff
In your case:
在你的情况下：
SELECT start_time, end_time,
FLOOR((end_time-start_time)*24) + ((end_time-start_time)*24-FLOOR((end_time-start_time)*24))/100*60 AS hours_diff
FROM come_leav
回答by Kevin Burton
try this:
尝试这个：
SELECT
TRIM(TO_CHAR(TRUNC(((86400*(end_time - start_time))/60)/60)-24*(trunc((((86400*(end_time - start_time))/60)/60)/24)),'00')) ||'.'||
TRIM(TO_CHAR(TRUNC((86400*(end_time - start_time))/60)-60*(trunc(((86400*(end_time - start_time))/60)/60)),'00')) ||'.' as duration
FROM come_leav;
回答by vmatyi
Edit: if you need a number, then
编辑：如果你需要一个号码，那么
trunc(end_date-start_date)*24+
to_number(to_char(trunc(sysdate)+(end_date-start_date),'HH24.MI'))
For string result, if delta is LESS THAN 24H: I would go with
对于字符串结果，如果 delta 小于 24H：我会选择
to_char(trunc(sysdate)+(end_date-start_date),'HH24.MI')
or ...'HH24:MI:SS', but thats my personal preference.
或者...'HH24:MI:SS'，但那是我个人的喜好。
for longer than 24H terms, I would prefix with
对于超过 24 小时的条款，我会加上前缀
trunc(end_date-start_date)||"days "||
to_char(trunc(sysdate)+(end_date-start_date),'HH24.MI')
Yes, as oracle counts in days, with seconds precision, you are dealing with arithmetical problems. Once because you are only handling minutes (so you might round your number to trunc(days*24*60+0.5)/24/60), but the binary arithmetic imprecision on the number 1/24/60 might still cause you troubles.
是的，因为 oracle 以天为单位，以秒为精度，您正在处理算术问题。曾经因为您只处理分钟（因此您可能会将您的数字四舍五入到trunc(days*24*60+0.5)/24/60），但是数字 1/24/60 的二进制算术不精确可能仍然会给您带来麻烦。
Edit2.1:
编辑2.1：
to_char(24*(trunc(end_date)-trunc(start_date))+to_number(to_char(end_date,'HH24.MI'))-to_number(to_char(start_date,'HH24.MI')),'99999.99')
But The result could be quite confusing for the average, as the decimal 7.50 would suggest seven and a half hour, or at least 7 hour 50 minutes, opposed to the elapsed time of 7 hours 10 minutes.
但是对于平均值而言，结果可能会令人困惑，因为小数点 7.50 表示七个半小时，或至少 7 小时 50 分钟，而不是 7 小时 10 分钟的经过时间。
回答by Ratan Kumar Nahak
This query is very usefull for me and if any body want diffrence between start_date and end_date with time like HH:MI:SS please use this query.
这个查询对我来说非常有用，如果有任何机构想要像 HH:MI:SS 这样的 start_date 和 end_date 之间的差异，请使用这个查询。
SELECT
TRIM(TO_CHAR(TRUNC(((86400*(end_date - start_date))/60)/60)-24*(trunc((((86400(end_date - start_date))/60)/60)/24)),'00')) ||'.'||
TRIM(TO_CHAR(TRUNC((86400*(actual_completion_date - actual_start_date))/60)-60*(trunc(((86400*(end_date - start_date))/60)/60)),'00')) ||'.'||
TRIM(TO_CHAR(TRUNC((86400*(end_date - start_date)))-60*(trunc((86400*(end_date - actual_start_date))/60)),'00')) as duration
FROM fnd_concurrent_requests;
回答by vapcguy
This is a very ugly way to do it, and this first part doesn't exactly question by the OP, but it gives a way to get results by subtracting 2 date fields -- in my case, the CREATED_DATEand today represented by SYSDATE:
这是一种非常丑陋的方法，第一部分并没有完全受到 OP 的质疑，但它提供了一种通过减去 2 个日期字段来获得结果的方法——在我的情况下，由CREATED_DATE表示的和今天SYSDATE：
SELECT FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE, SYSDATE)) / 12) || ' years, '
|| (FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE, SYSDATE))) -
(FLOOR(ABS(MONTHS_BETWEEN(CREATED_DATE, SYSDATE)) / 12)) * 12) || ' months, '
-- we take total days - years(as days) - months(as days) to get remaining days
|| FLOOR((SYSDATE - CREATED_DATE) - -- total days
(FLOOR((SYSDATE - CREATED_DATE)/365)*12)*(365/12) - -- years, as days
-- this is total months - years (as months), to get number of months,
-- then multiplied by 30.416667 to get months as days (and remove it from total days)
FLOOR(FLOOR(((SYSDATE - CREATED_DATE)/365)*12 - (FLOOR((SYSDATE - CREATED_DATE)/365)*12)) * (365/12)))
|| ' days, '
-- Here, we can just get the remainder decimal from total days minus
-- floored total days and multiply by 24
|| FLOOR(
((SYSDATE - CREATED_DATE)-(FLOOR(SYSDATE - CREATED_DATE)))*24
)
|| ' hours, '
-- Minutes just use the unfloored hours equation minus floored hours,
-- then multiply by 60
|| ROUND(
(
(
((SYSDATE - CREATED_DATE)-(FLOOR(SYSDATE - CREATED_DATE)))*24
) -
FLOOR((((SYSDATE - CREATED_DATE)-(FLOOR(SYSDATE - CREATED_DATE)))*24))
)*60
)
|| ' minutes'
AS AGE FROM MyTable`
It delivers the output as x years, x months, x days, x hours, x minutes. It could be reformatted however you like by changing the concatenated strings.
它以 x 年、x 个月、x 天、x 小时、x 分钟的形式提供输出。可以通过更改连接的字符串来重新格式化。
To more directly answer the question, I've gone ahead and written out how to get the total hours with minutes as hours.minutes:
为了更直接地回答这个问题，我已经写出了如何用分钟获得总小时数为hours.minutes：
select
((FLOOR(end_date - start_date))*24)
|| '.' ||
ROUND(
(
(
((end_date - start_date)-(FLOOR(end_date - start_date)))*24
) -
FLOOR((((end_date - start_date)-(FLOOR(end_date - start_date)))*24))
)*60
)
from
come_leav;
回答by roselan
you can work with the extract:
您可以使用提取物：
SELECT start_time, end_time, extract(HOUR FROM end_time-start_time) || '.' || extract(MINUTE FROM end_time-start_time)
From come_leav;
