Oracle如何计算两个日期的差值并转换为HH:MI时分格式

原理说明 #

Oracle中DATE类型相减返回值单位为天，要转为HH:MI格式，需要先将天差值转换为总分钟/总小时数再拆分拼接，或通过时间间隔函数处理。

方法1：算术计算法（兼容性最好，支持跨天差值） #

直接通过数学运算拆分小时、分钟，补前导零后拼接：

SELECT 
  LPAD(FLOOR((end_dt - start_dt) * 24), 2, '0') 
  || ':' 
  || LPAD(MOD(FLOOR((end_dt - start_dt) * 24 * 60), 60), 2, '0') AS time_diff
FROM (
  SELECT 
    to_date('13.05.2021 09:30','DD.MM.YYYY HH24:MI') AS end_dt,
    to_date('13.05.2021 08:15','DD.MM.YYYY HH24:MI') AS start_dt
  FROM dual
);

• 输出结果：01:15
• 优势：差值超过24小时也会正常累计显示，比如差26小时30分会输出26:30

方法2：时间间隔函数法（代码更简洁） #

用NUMTODSINTERVAL函数将天差值转为时间间隔类型，再截取需要的部分：

SELECT SUBSTR(
  NUMTODSINTERVAL(end_dt - start_dt, 'DAY'),
  12, 5
) AS time_diff
FROM (
  SELECT 
    to_date('13.05.2021 09:30','DD.MM.YYYY HH24:MI') AS end_dt,
    to_date('13.05.2021 08:15','DD.MM.YYYY HH24:MI') AS start_dt
  FROM dual
);

• 注意：如果差值超过1天，该方法默认只取当天的小时分钟部分，比如差25小时15分会输出01:15，如果需要累计小时数请使用方法1。

内容的提问来源于stack exchange，提问作者BOMBERMAN