问题描述
我需要以hh:mm:ss格式找到时间之间的差异
i need to find the difference between the time in the format hh:mm:ss
select msglog.id,max(msglog.timestamp) enddate,
min(msglog.timestamp) startdate,
enddate - startdate
from MESSAGELOG msglog
group by id
在上面的查询中,msglog.timestamp的类型为DATE.
In the abovequery msglog.timestamp is of type DATE.
如何在oracle中以正确的格式获取经过时间或时间之间的差异?
How can I get the elapsed time or diff between the time in the correct format in oracle?
推荐答案
当您减去两个DATE
值(例如enddate - startdate
)时,您得到的天数差以十进制精度表示,因此例如1.5意味着1 1/2天或36个小时.您可以使用大量数学将其转换为HH:MI:SS
,但是更简单的方法是使用函数:
When you subtract two DATE
values like enddate - startdate
you 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:SS
using a lot of math, but an easier way is to convert the decimal value to an INTERVAL DAY TO SECOND
value using the NUMTODSINTERVAL
function:
NUMTODSINTERVAL(enddate - startdate, 'DAY')
您可能会认为TO_CHAR
函数可以将其格式化为HH:MI:SS
,但似乎无法正常工作.您可以改用EXTRACT
和TO_CHAR
来确保得到前导零:
You'd think the TO_CHAR
function would be able to format this as HH:MI:SS
, but it doesn't seem to work that way. You can use EXTRACT
instead, and TO_CHAR
to make sure you get leading zeros:
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')
格式代码的00
部分指定两位数字,如果需要,则以0开头. FM
部分去除了格式化结果中的前导空格,如果需要,该空格保留为负号.
The 00
part of the format code specifies two digits, with a leading zero if needed. The FM
part gets rid of the leading space in the formatted result, which is reserved for a negative sign if needed.
还请注意,您的查询将获取汇总值,并在同一SELECT
列表中使用它们. Oracle不允许您这样做.尝试这样的事情:
Also note that your query gets aggregate values and uses them in the same SELECT
list. Oracle won't let you do this. Try something like this instead:
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
这篇关于在oracle sql中找到两个日期之间的经过时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!