Oracle中如何从日期列计算并格式化显示总耗时

你已经找对了方向，问题核心在于日期相减得到的是天数的小数形式，我们需要把这个数值转换成「Xh Ym」或「Ym」的可读格式，而且最好避免来回转换日期字符串——既低效又容易引入格式错误。

修改后的SQL语句 #

SELECT 
    table_name,
    TO_CHAR(min_dato, 'dd.mm.yyyy hh24:mi') AS start_time,
    TO_CHAR(max_dato, 'dd.mm.yyyy hh24:mi') AS end_time,
    -- 按需求格式化总耗时
    CASE 
        WHEN TRUNC((max_dato - min_dato) * 24) > 0 THEN
            TRUNC((max_dato - min_dato) * 24) || 'h ' || MOD(ROUND((max_dato - min_dato) * 24 * 60), 60) || 'm'
        ELSE
            MOD(ROUND((max_dato - min_dato) * 24 * 60), 60) || 'm'
    END AS total_time
FROM (
    SELECT 
        table_name,
        MIN(dato) AS min_dato,
        MAX(dato) AS max_dato
    FROM TMP_AUDIT_LOG t 
    WHERE FLAG='Y' AND TR_MODE='EXPORT' 
    GROUP BY t.table_name 
    ORDER BY min_dato
);

关键逻辑解释 #

1. 保留原始日期类型：内层查询直接对DATO字段计算MIN和MAX，不需要先转成字符串再转回来，既提升效率又避免格式兼容问题。
2. 时间差计算：
   • (max_dato - min_dato)得到天数（小数），乘以24得到小时数，乘以60得到总分钟数；
   • TRUNC((max_dato - min_dato)*24)提取整数小时部分；
   • MOD(ROUND(...),60)提取剩余的分钟数（用ROUND处理秒数带来的小数误差）。
3. 动态格式化输出：用CASE语句判断是否需要显示小时部分，完全匹配你期望的输出样式。

可选简化方案（Oracle 12c+） #

如果你的Oracle版本是12c及以上，可以用NUMTODSINTERVAL函数直接生成时间间隔类型，提取小时和分钟更直观：

SELECT 
    table_name,
    TO_CHAR(min_dato, 'dd.mm.yyyy hh24:mi') AS start_time,
    TO_CHAR(max_dato, 'dd.mm.yyyy hh24:mi') AS end_time,
    CASE 
        WHEN EXTRACT(HOUR FROM diff) > 0 THEN
            EXTRACT(HOUR FROM diff) || 'h ' || EXTRACT(MINUTE FROM diff) || 'm'
        ELSE
            EXTRACT(MINUTE FROM diff) || 'm'
    END AS total_time
FROM (
    SELECT 
        table_name,
        MIN(dato) AS min_dato,
        MAX(dato) AS max_dato,
        NUMTODSINTERVAL(MAX(dato)-MIN(dato), 'DAY') AS diff
    FROM TMP_AUDIT_LOG t 
    WHERE FLAG='Y' AND TR_MODE='EXPORT' 
    GROUP BY t.table_name 
    ORDER BY min_dato
);

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