SQL 在oracle sql中查找两个日期之间经过的时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21097004/
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
find the elapsed time between two dates in oracle sql
提问by suni
i need to find the difference between the time in the format hh:mm:ss
我需要以 hh:mm:ss 格式找到时间之间的差异
select msglog.id,max(msglog.timestamp) enddate,
min(msglog.timestamp) startdate,
enddate - startdate
from MESSAGELOG msglog
group by id
In the abovequery msglog.timestamp is of type DATE.
在上面的查询中 msglog.timestamp 是 DATE 类型。
How can I get the elapsed time or diff between the time in the correct format in oracle?
如何在oracle中以正确格式获取经过的时间或时间之间的差异？
回答by Ed Gibbs
When you subtract two DATEvalues like enddate - startdateyou get the difference in days with decimal accuracy, so for example 1.5 would mean 1 1/2 days or 36 hours. You can convert that to HH:MI:SSusing a lot of math, but an easier way is to convert the decimal value to an INTERVAL DAY TO SECONDvalue using the NUMTODSINTERVALfunction:
当您减去两个DATE值时，就像enddate - startdate您得到十进制精度的天数差异一样，例如 1.5 表示 1 1/2 天或 36 小时。您可以将其转换为HH:MI:SS使用大量数学，但更简单的方法是INTERVAL DAY TO SECOND使用以下NUMTODSINTERVAL函数将十进制值转换为值：
NUMTODSINTERVAL(enddate - startdate, 'DAY')
You'd think the TO_CHARfunction would be able to format this as HH:MI:SS, but it doesn't seem to work that way. You can use EXTRACTinstead, and TO_CHARto make sure you get leading zeros:
您可能认为该TO_CHAR函数能够将其格式化为HH:MI:SS，但它似乎并不能那样工作。您可以改用EXTRACT并TO_CHAR确保获得前导零：
TO_CHAR(EXTRACT(HOUR FROM NUMTODSINTERVAL(enddate-startdate, 'DAY')), 'FM00')
|| ':' ||
TO_CHAR(EXTRACT(MINUTE FROM NUMTODSINTERVAL(enddate-startdate, 'DAY')), 'FM00')
|| ':' ||
TO_CHAR(EXTRACT(SECOND FROM NUMTODSINTERVAL(enddate-startdate, 'DAY')), 'FM00')
The 00part of the format code specifies two digits, with a leading zero if needed. The FMpart gets rid of the leading space in the formatted result, which is reserved for a negative sign if needed.
00格式代码的部分指定了两位数字，如果需要，可以使用前导零。该FM部分去掉了格式化结果中的前导空格，如果需要，它会保留给负号。
Also note that your query gets aggregate values and uses them in the same SELECTlist. Oracle won't let you do this. Try something like this instead:
另请注意，您的查询获取聚合值并在同一SELECT列表中使用它们。Oracle 不会让你这样做。试试这样的：
WITH StartEndByID AS (
SELECT
msglog.id,
NUMTODSINTERVAL(max(msglog.timestamp) - min(msglog.timestamp), 'DAY') elapsed
FROM messagelog msglog
GROUP BY id
)
SELECT
id,
TO_CHAR(EXTRACT(HOUR FROM elapsed), 'FM00') || ':' ||
TO_CHAR(EXTRACT(MINUTE FROM elapsed), 'FM00') || ':' ||
TO_CHAR(EXTRACT(SECOND FROM elapsed), 'FM00') AS ElapsedHHMISS
FROM StartEndByID
回答by GriffeyDog
Date arithmetic in Oracle results in a number expressed in days. So, to convert to hours, you would multiply by 24 and then truncto get an integral number:
Oracle 中的日期算术产生以天表示的数字。因此，要转换为小时，您需要乘以 24，然后trunc得到一个整数：
trunc(24 * (enddate - startdate))
To get minutes, convert the days value to minutes and mod()that with 60:
要获得分钟，请将天数转换为分钟，然后将其转换为mod()60：
mod(trunc(24 * 60 * (enddate - startdate)), 60)
For seconds, again convert days to seconds and mod()that with 60:
对于秒，再次将天转换为秒，然后转换为mod()60：
mod(trunc(24 * 60 * 60 * (enddate - startdate)), 60)
Now you can put these together to get the string value you need.
现在您可以将这些放在一起以获得您需要的字符串值。
回答by ok2307
To get the diff in seconds you can use this
要在几秒钟内获得差异，您可以使用它
select round(24 * 60 * 60* (TO_DATE('2017/02/17 9:32:25', 'YYYY/MM/DD HH:MI:SS') - TO_DATE('2017/02/17 8:30:30', 'YYYY/MM/DD HH:MI:SS'))) from dual;
