如何在Oracle PL/SQL中计算工作时长？负时长如何正确显示？

问题原因 #

你当前的查询里，7.5 + 0.75 - 8算出来是0.25小时（15分钟），所以返回00:15；但如果计算结果是负数（比如实际工作+休息时长小于计划8小时），原方法会返回前一天的时间（比如-0.25小时会显示23:45），没法直接显示负号。这是因为TIME类型是循环计时的，负数偏移会回绕到前一天，to_char默认不会输出负号。

可行解决方案 #

方案1：判断正负后手动拼接格式 #

先把差值转成总分钟数，再根据正负输出带符号的结果：

SELECT
    CASE
        WHEN (工作时长 + 休息时长 - 计划时长) < 0 
        THEN '-' || TO_CHAR(ABS((工作时长 + 休息时长 - 计划时长)*60), 'FM00') || '分钟'
        ELSE TO_CHAR((工作时长 + 休息时长 - 计划时长)*60, 'FM00') || '分钟'
    END AS 时长差值
FROM dual;

如果需要-00:15这种时分格式：

SELECT
    CASE
        WHEN (7.5 + 0.75 - 8) < 0 
        THEN '-' || TO_CHAR(TRUNC(ABS(7.5 + 0.75 - 8)), 'FM00') || ':' || TO_CHAR(MOD(ABS(7.5 + 0.75 - 8)*60, 60), 'FM00')
        ELSE TO_CHAR(TRUNC(7.5 + 0.75 - 8), 'FM00') || ':' || TO_CHAR(MOD((7.5 + 0.75 - 8)*60, 60), 'FM00')
    END AS 时长差值
FROM dual;

方案2：用Oracle 12c+的INTERVAL格式化（最简洁） #

Oracle 12c及以上版本支持直接格式化INTERVAL类型，通过格式模型里的S来显示符号：

SELECT
    TO_CHAR(INTERVAL '1' HOUR * (7.5 + 0.75 - 8), 'SFMHH24:MI')
FROM dual;

这个语句会在差值为负时显示-00:15，为正时显示+00:15。如果不需要正号，加个CASE判断去掉即可：

SELECT
    CASE
        WHEN (7.5 + 0.75 - 8) >= 0 
        THEN TO_CHAR(INTERVAL '1' HOUR * (7.5 + 0.75 - 8), 'FMHH24:MI')
        ELSE TO_CHAR(INTERVAL '1' HOUR * (7.5 + 0.75 - 8), 'SFMHH24:MI')
    END AS 时长差值
FROM dual;

方案3：直接输出带符号的分钟数 #

如果只需要分钟数的结果，这个方法最简单：

SELECT
    TO_CHAR((7.5 + 0.75 - 8)*60, 'SFM9999') || '分钟'
FROM dual;

负15分钟会显示-15分钟，正15分钟显示+15分钟，不需要正号的话同样用CASE处理。

最优推荐 #

如果你的Oracle版本是12c及以上，方案2的优化版（去掉正号）是最简洁高效的，完全匹配你的需求。

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