SQL/AS400数据库HH.MM.SS格式工作时长SUM求和方法咨询

核心逻辑 #

working_hours为字符串格式的时分秒标识，直接使用SUM函数会按数值或字符串规则聚合，无法得到正确的时长总和：

• 第一步：拆分每条记录的时、分、秒，换算为总秒数后求和
• 第二步：将求和后的总秒数反向格式化为HH.MM.SS格式，补全前导零

AS400（DB2 for i）实现代码 #

SELECT 
    -- 拼接HH.MM.SS格式，不足两位补前导零
    LPAD(TRIM(CHAR(INT(total_seconds / 3600))), 2, '0') 
    || '.' 
    || LPAD(TRIM(CHAR(INT(MOD(total_seconds, 3600) / 60))), 2, '0')
    || '.' 
    || LPAD(TRIM(CHAR(MOD(MOD(total_seconds, 3600), 60))), 2, '0') AS total_working_hours
FROM (
    -- 计算所有时长的总秒数和
    SELECT SUM(
        INT(SUBSTR(working_hours, 1, 2)) * 3600 
        + INT(SUBSTR(working_hours, 4, 2)) * 60 
        + INT(SUBSTR(working_hours, 7, 2))
    ) AS total_seconds
    FROM Attendance
    -- 如需按员工分组求和，可取消注释下行
    -- GROUP BY empcode
) AS temp_sum

通用SQL适配说明 #

其他数据库环境仅需调整对应函数名即可，逻辑完全一致：

• MySQL：替换CHAR()为CAST(xxx AS CHAR)，其余函数LPAD、SUBSTR、MOD均通用
• Oracle：替换TRIM(CHAR(xxx))为TO_CHAR(xxx)，其余函数通用
• SQL Server：替换LPAD为RIGHT('0'+CAST(xxx AS VARCHAR),2)，MOD替换为%取模运算符

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