在oracle sql中找到两个日期之间的经过时间 [英] find the elapsed time between two dates in oracle sql

问题描述

我需要以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中找到两个日期之间的经过时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！