sql - HH :MM:SS 中的 Oracle SQL 时差

Question

我正在尝试获取 Oracle 数据库中的时差。除了一行之外，所有行的时间差都可以。这是查询

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_end: '2020-02-20 13:00:20'
call_start: '2020-02-20 12:56:03'

返回的结果是'11:55:43'，这是错误的。因为正确答案应该是'00:04:17'

Answer 1

看起来您的表已经将调用开始/结束时间存储为日期，并且您正在进行从日期到字符串的隐式转换，然后再转换回日期。如果在 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:03 之间的时差是 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，但不确定，已经有一段时间了)使用他们自己的首选项/设置而不是遵守 session 的 NLS 设置。但这对 Oracle 在必须进行隐式转换时的内部行为没有影响。