以 HH:MM:SS 表示的日期之间的 Oracle SQL 小时数差异 [英] Oracle SQL Hours Difference between dates in HH:MM:SS

问题描述

我已经有一个字符串来计算我从堆栈中得到的两个日期之间的小时差:

I already have a string to calculate the difference in hours between 2 dates which I'd got from stack:

24 *  (to_date(to_char(stp.created_date,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss') -
(to_date(to_char(adhh.created_date,'YYYY-MM-DD hh24:mi:ss'),'YYYY-MM-DD hh24:mi:ss'))) diff_hours

但我想把它看成 HH:MM:SS

But I want to see this as HH:MM:SS

以 2 个日期/时间为例:STP 日期 26-Feb-18 12.59.21ADHH 日期 26-Feb-18 12.59.32

Here's 2 dates/times as example: STP date 26-Feb-18 12.59.21 ADHH date 26-Feb-18 12.59.32

所以我想说差异是 00:00:11(11 秒)

So I want it to say difference is 00:00:11 (11 seconds)

目前我得到的结果为 -0.003 小时

at the moment I get the result as -0.003 hours

一如既往地提前致谢

推荐答案

假设列已经是日期，因此您正在执行的与字符串之间的转换毫无意义，并且差异总是小于一天，你可以这样做:

Assuming the columns are already dates, and the therefore the conversion to and from strings you are doing is pointless, and that the difference is always going to be less than a day, you could do:

to_char(date '1970-01-01' + abs(stp.created_date - adhh.created_date), 'HH24:MI:SS')

这将获取日期之间的差异作为一天的一小部分；通过 abs() 确保它是正的；然后将该分数加回一个名义日期，其时间为午夜.然后将其转换为字符串，只查看时间.

This gets the difference between the dates as a fraction of a day; makes sure it's positive via abs(); and then adds that fraction back on to a nominal date, which has its time as midnight. Then it converts that to a string, looking only at the time.

快速演示:

-- CTEs to supply the two date/times
with stp (created_date) as (
  select cast(timestamp '2018-02-26 12:59:21' as date) from dual
),
adhh (created_date) as (
  select cast(timestamp '2018-02-26 12:59:32' as date) from dual
)
select to_char(date '1970-01-01' + abs(stp.created_date - adhh.created_date), 'HH24:MI:SS')
from stp cross join adhh;

TO_CHAR(
--------
00:00:11

如果差异可以超过一天，但不能超过一年，您可以将格式模型更改为类似于 'FMDDD FMHH24:MI:SS' 的内容，这将在开始时显示全天.

If the difference can exceed a day, but not a year, you could change the format model to something like 'FMDDD FMHH24:MI:SS', which will show full days at the start.

这篇关于以 HH:MM:SS 表示的日期之间的 Oracle SQL 小时数差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！