Oracle SQL：对存储为hh24:mi:ss的VARCHAR2列按组求和

首先得明确：直接对VARCHAR2类型的LOAD_PROCESS_TIME执行SUM()是行不通的——字符串没法直接做数值求和。我们需要先把hh24:mi:ss格式的字符串转换成总秒数，求和后再转回时分秒格式。

完整实现SQL #

SELECT 
    TO_CHAR(TRUNC(END_TIME, 'DD'), 'DD-MON-YY') AS DATE_GROUP,
    -- 把总秒数转成hh24:mi:ss格式，FM00确保两位数无空格
    TO_CHAR(FLOOR(total_seconds / 3600), 'FM00') || ':' ||
    TO_CHAR(FLOOR(MOD(total_seconds, 3600) / 60), 'FM00') || ':' ||
    TO_CHAR(MOD(total_seconds, 60), 'FM00') AS TOTAL_LOAD_PROCESS_TIME
FROM (
    SELECT 
        TRUNC(END_TIME, 'DD') AS date_trunc,
        -- 将每个LOAD_PROCESS_TIME转换为总秒数后求和
        SUM(
            TO_NUMBER(SUBSTR(LOAD_PROCESS_TIME, 1, 2)) * 3600 + -- 小时转秒
            TO_NUMBER(SUBSTR(LOAD_PROCESS_TIME, 4, 2)) * 60 +   -- 分钟转秒
            TO_NUMBER(SUBSTR(LOAD_PROCESS_TIME, 7, 2))         -- 秒数
        ) AS total_seconds
    FROM SCHEMA.MY_TABLE
    WHERE END_TIME > SYSDATE - 14
      -- 可选：过滤格式无效的记录，避免转换报错
      AND REGEXP_LIKE(LOAD_PROCESS_TIME, '^\d{2}:\d{2}:\d{2}$')
    GROUP BY TRUNC(END_TIME, 'DD')
)
ORDER BY date_trunc;

代码细节说明 #

1. 子查询核心处理：

   • 用SUBSTR拆分LOAD_PROCESS_TIME：提取前2位是小时、第4-5位是分钟、第7-8位是秒。
   • 把各部分转成数字后，分别换算成秒数再求和，得到当天的总耗时秒数total_seconds。
   • 加上REGEXP_LIKE过滤是为了避免因格式错误（比如非hh24:mi:ss的字符串）导致的转换报错，可根据实际数据情况选择是否保留。

2. 外层格式还原：

   • 用FLOOR(total_seconds / 3600)计算总小时数，MOD(total_seconds, 3600)得到剩余秒数。
   • 剩余秒数再除以60得到分钟数，最后MOD(total_seconds, 60)得到剩余秒数。
   • TO_CHAR(..., 'FM00')确保每个时间部分都是两位数，且不会出现前导空格（比如1小时会显示为01而不是 1）。

测试示例结果 #

针对你提供的两条测试数据，执行后会得到：

如果某天有多条记录，总耗时会自动累加（比如两条记录分别是01:00:00和02:00:00，结果会显示03:00:00）。

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