Oracle SQL time difference in HH:MM:SS

看起来您的表已经将调用开始/结束时间存储为日期，并且您正在进行从日期到字符串的隐式转换，然后再转换回日期。如果NLS_DATE_FORMAT设置中有HH而不是HH24，则可以看到该结果：

alter session set nls_date_format = 'YYYY-MM-DD HH:MI:SS';
with calls (call_end, call_start) as (
select cast(timestamp '2020-02-20 13:00:20' as date), cast(timestamp '2020-02-20 12:56:03' as date) from dual
)
SELECT MAX(REGEXP_SUBSTR (CAST(TO_DATE(call_end, 'YYYY/MM/DD HH24:MI:SS') AS TIMESTAMP) - CAST(TO_DATE(call_start, 'YYYY/MM/DD HH24:MI:SS') AS TIMESTAMP), 'd{2}:d{2}:d{2}')) AS call_time
FROM calls;
CALL_TIME                  
---------------------------
11:55:43

当你做

TO_DATE(call_end, 'YYYY/MM/DD HH24:MI:SS')

因为这已经是你真正在做的约会了：

TO_DATE(TO_CHAR(call_end, <NLS_DATE_FORMAT>), 'YYYY/MM/DD HH24:MI:SS')

所以我的设置(你的设置必须相似，也许用斜线而不是破折号(是：

TO_DATE(TO_CHAR(call_end, 'YYYY-MM-DD HH:MI:SS'), 'YYYY/MM/DD HH24:MI:SS')

并且HH和HH24之间的失配变得更加明显。因此，您实际上是在将字符串2020-02-20 01:00:20和2020-02-20 12:56:03转换回日期，01:00:20与12:56:02之间的时间差为11:55:43。实际上，这是减去11小时：

SELECT CAST(TO_DATE(call_end, 'YYYY/MM/DD HH24:MI:SS') AS TIMESTAMP) - CAST(TO_DATE(call_start, 'YYYY/MM/DD HH24:MI:SS') AS TIMESTAMP)
FROM calls;
CAST(TO_DATE(CALL_E
-------------------
-00 11:55:43.000000

但是您的正则表达式没有接收到这一点。

因为它们是日期，所以完全跳过转换的这一部分，如果你想从以下时间间隔开始工作，只需直接转换为时间戳：

SELECT MAX(REGEXP_SUBSTR (CAST(call_end AS TIMESTAMP) - CAST(call_start AS TIMESTAMP), 'd{2}:d{2}:d{2}')) AS call_time
FROM calls;
CALL_TIME                  
---------------------------
00:04:17

或者使用子字符串而不是正则表达式，如@MTO所示。

你也可以把它们作为日期，把差值作为一天的一小部分，在午夜把它加到任何名义日期上，然后把得到的日期转换成字符串：

SELECT TO_CHAR(date '2000-01-01' + MAX(call_end - call_start), 'HH24:MI:SS') AS call_time
FROM calls;
CALL_TIM
--------
00:04:17

对于持续时间超过24小时的呼叫，这将无法正常工作(过去经常在调制解调器呼叫中看到这种情况，但仍然可能发生(；但你的间歇期也不会到来。两者都忽略任何一整天，只显示剩余时间。当然，有一些方法可以处理，但你需要决定如何显示它——用单独的天数(就像间隔一样(，或者允许"小时"数超过24……但你可能会超过99小时。。。

您的问题显示呼叫结束时间为2020-02-20 13:00:20，这表明您的客户在查询表时就是这样显示的。一些客户端(我认为PL/SQL Developer，但不确定，已经有一段时间了(使用自己的首选项/设置，而不是遵守会话的NLS设置。但当Oracle必须进行隐式转换时，这对其内部行为没有影响。

我不确定您是否希望将结果作为间隔或时间戳，但这应该符合您的要求：

select t.*, call_end - call_start,
substr(to_char(call_end - call_start, 'HH24:MI:SS'), 12, 8) as str    from (select timestamp '2020-02-20 13:00:20.000' as call_end,
timestamp '2020-02-20 12:56:03.000' as call_start
from dual) t

这是一个数据库<>不停摆弄

假设您的数据类型是字符串(您确实应该将它们存储为DATE数据类型(，您可以使用：

SELECT SUBSTR(
MAX(
( TO_DATE( call_end,   'YYYY-MM-DD HH24:MI:SS' )
- TO_DATE( call_start, 'YYYY-MM-DD HH24:MI:SS' )
) DAY(1) TO SECOND
),
4,
8
) AS call_time
FROM   calls

如果它们已经是DATE数据类型，那么在MAX聚合中，只需使用：

( call_end - call_start ) DAY(1) TO SECOND

因此，对于您的数据：

CREATE TABLE calls ( call_end, call_start ) AS
SELECT '2020-02-20 13:00:20', '2020-02-20 12:56:03' FROM DUAL

该输出：

|CALL_TIME||：--------||00:04:17 |

db<>小提琴这里

请参阅下面的响应，但这只能在24小时内工作，并返回'00'

WITH sample_lt AS(
SELECT '2020-02-20 12:56:03' START_TIME, '2020-02-20 13:00:20' END_TIME FROM dual
)
SELECT start_time,
end_time,
TO_CHAR (TRUNC (SYSDATE) + (to_date(end_time, 'yyyy-mm-dd HH24:MI:SS') -
to_date(start_time, 'yyyy-mm-dd HH24:MI:SS')
) , 'hh24:mi:ss' ) duration
FROM sample_lt ;