将HHH:MM:SS转换为秒：小时超99时SQL语句失效求助

嘿，这个问题我之前处理过！你原来的写法核心问题出在固定位置截取字符串——当小时数超过两位时，LEFT(COLUMN,2)只能取到前两位，完全丢失了后续的数字，自然会计算错误。咱们换个思路：按冒号:来拆分时长字符串，不管小时是1位、2位还是N位，都能准确提取各部分数值。

下面针对不同主流SQL方言给出解决方案：

SQL Server 方案 #

利用REPLACE把冒号换成点，再用PARSENAME按点拆分（PARSENAME默认从右往左取拆分后的部分），配合TRY_CAST避免格式错误导致的报错：

ISNULL(
    TRY_CAST(PARSENAME(REPLACE(你的列名, ':', '.'), 3) AS INT) * 3600 +
    TRY_CAST(PARSENAME(REPLACE(你的列名, ':', '.'), 2) AS INT) * 60 +
    TRY_CAST(PARSENAME(REPLACE(你的列名, ':', '.'), 1) AS INT),
    0
)

举个例子：832:24:12会被转换成832.24.12，PARSENAME(...,3)取到832，PARSENAME(...,2)取到24，PARSENAME(...,1)取到12，计算后就是正确的总秒数。

MySQL 方案 #

用SUBSTRING_INDEX按冒号拆分，分别提取小时、分钟、秒：

IFNULL(
    CAST(SUBSTRING_INDEX(你的列名, ':', 1) AS UNSIGNED) * 3600 +
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(你的列名, ':', 2), ':', -1) AS UNSIGNED) * 60 +
    CAST(SUBSTRING_INDEX(你的列名, ':', -1) AS UNSIGNED),
    0
)

• SUBSTRING_INDEX(你的列名, ':', 1)：取第一个冒号左侧的小时部分
• SUBSTRING_INDEX(SUBSTRING_INDEX(你的列名, ':', 2), ':', -1)：取中间的分钟部分
• SUBSTRING_INDEX(你的列名, ':', -1)：取最后一个冒号右侧的秒部分

PostgreSQL 方案 #

用STRING_TO_ARRAY把字符串转成数组，再按索引取值：

COALESCE(
    (STRING_TO_ARRAY(你的列名, ':')::INT[])[1] * 3600 +
    (STRING_TO_ARRAY(你的列名, ':')::INT[])[2] * 60 +
    (STRING_TO_ARRAY(你的列名, ':')::INT[])[3],
    0
)

如果担心格式错误，可以用TRY_CAST替换强制转换，比如TRY_CAST((STRING_TO_ARRAY(你的列名, ':'))[1] AS INT)，避免因无效数据导致语句报错。

额外提示 #

如果你的数据里存在格式不规范的时长（比如非数字、缺少分隔符），一定要用TRY_CAST（SQL Server）、TRY_CONVERT这类容错函数，这样转换失败时会返回NULL，最后被ISNULL/IFNULL/COALESCE替换成0，保证语句稳定运行。

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