如何在SQL/PLSQL中将Decimal类型数值转换为DD:HH:MM时长格式

假设你的Decimal值代表总小时数（比如示例中的1.5就是1.5小时），下面提供两种实用实现方式：

1. 标准DD:HH:MM格式输出 #

如果需要严格的DD:HH:MM格式（不足两位补零），可以用以下SQL语句：

WITH sample_data AS (
    SELECT 1.5 AS total_hours FROM dual
)
SELECT
    LPAD(TRUNC(total_hours / 24), 2, '0') || ':' ||
    LPAD(TRUNC(MOD(total_hours, 24)), 2, '0') || ':' ||
    LPAD(ROUND(MOD(total_hours * 60, 60)), 2, '0') AS dd_hh_mm
FROM sample_data;

执行结果：00:01:30

各部分逻辑说明：

• TRUNC(total_hours / 24)：计算总天数（取整数部分）
• TRUNC(MOD(total_hours, 24))：计算剩余小时数（总小时对24取模后取整）
• ROUND(MOD(total_hours * 60, 60))：计算分钟数（总小时转分钟后对60取模，四舍五入；不需要四舍五入可替换为TRUNC）
• LPAD(..., 2, '0')：给天数、小时、分钟补前导零，确保两位格式

2. 友好文本格式输出（如示例的"1小时30分钟"） #

如果不需要严格的冒号分隔格式，而是根据实际值显示非零部分的友好文本，可以用CASE语句实现：

WITH sample_data AS (
    SELECT 1.5 AS total_hours FROM dual
)
SELECT
    CASE
        WHEN TRUNC(total_hours / 24) > 0 THEN
            TRUNC(total_hours / 24) || '天' ||
            LPAD(TRUNC(MOD(total_hours, 24)), 2, '0') || '小时' ||
            LPAD(ROUND(MOD(total_hours * 60, 60)), 2, '0') || '分钟'
        WHEN TRUNC(MOD(total_hours, 24)) > 0 THEN
            TRUNC(MOD(total_hours, 24)) || '小时' ||
            LPAD(ROUND(MOD(total_hours * 60, 60)), 2, '0') || '分钟'
        ELSE
            ROUND(MOD(total_hours * 60, 60)) || '分钟'
    END AS duration_text
FROM sample_data;

执行结果：1小时30分钟

注意事项 #

如果你的Decimal值代表的是总天数（比如1.5天=36小时），只需调整各部分的计算逻辑：

• 天数：TRUNC(decimal_value)
• 剩余小时：TRUNC(MOD(decimal_value, 1) * 24)
• 分钟：ROUND(MOD(decimal_value * 24 * 60, 60))

把上述代码中的计算式替换即可得到正确结果。

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