找到oracle sql中两个日期之间的经过时间

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中以正确的格式获取经过的时间或差异?

3 个解决方案

#1

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:

当您减去两个DATE值(如enddate - startdate)时,您会得到带小数精度的天数差异,因此例如1.5表示1 1/2天或36小时。您可以使用大量数学将其转换为HH:MI:SS,但更简单的方法是使用NUMTODSINTERVAL函数将十进制值转换为INTERVAL DAY TO SECOND值:

  NUMTODSINTERVAL(enddate - startdate, 'DAY')

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函数可以将其格式化为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 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.

格式代码的00部分指定两个数字,如果需要,前导零。 FM部分摆脱了格式化结果中的前导空格,如果需要,可以为负号保留。

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:

另请注意,您的查询获取聚合值并在同一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

#2

Date arithmetic in Oracle results in a number expressed in days. So, to convert to hours, you would multiply by 24 and then trunc to get an integral number:

Oracle中的日期算术会生成以天为单位的数字。因此,要转换为小时,您将乘以24然后截断以获得整数:

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:

对于秒,再次将天数转换为秒,使用60转换为mod():

mod(trunc(24 * 60 * 60 * (enddate - startdate)), 60)

Now you can put these together to get the string value you need.

现在,您可以将它们放在一起以获取所需的字符串值。

#3

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;

#1

  NUMTODSINTERVAL(enddate - startdate, 'DAY')