如何将时间戳差值秒数转换为Days Hours:Minutes:SS或DD:HH:MM:SS格式

核心思路 #

先把总秒数拆分为完整天数和当日剩余秒数两部分，再将剩余秒数格式化为HH:MM:SS，最后拼接两部分得到目标格式。

通用SQL实现（适配多数数据库） #

假设Seconds是数值类型（若为带千位分隔符的字符串，需先做格式清理）：

SELECT
    Timestamp_A,
    Timestamp_B,
    Seconds,
    -- 生成 Days HH:MM:SS 格式结果
    CONCAT(
        LPAD(FLOOR(Seconds / 86400), 2, '0'),
        ' ',
        TO_CHAR(
            TIMESTAMP '1970-01-01 00:00:00' + INTERVAL '1' SECOND * MOD(Seconds, 86400),
            'HH24:MI:SS'
        )
    ) AS Converted
FROM Ex;

Snowflake专属优化写法 #

Snowflake支持直接对时间增量做格式化，写法更简洁：

SELECT
    Timestamp_A,
    Timestamp_B,
    Seconds,
    CONCAT(
        LPAD(FLOOR(Seconds / 86400), 2, '0'),
        ' ',
        TO_VARCHAR(TIMEADD(SECOND, MOD(Seconds, 86400), '00:00:00'), 'HH24:MI:SS')
    ) AS Converted
FROM Ex;

处理带逗号的Seconds列 #

如果Seconds是带千位分隔符的字符串，先转换为数值再计算：

SELECT
    Timestamp_A,
    Timestamp_B,
    Seconds,
    CONCAT(
        LPAD(FLOOR(TRY_CAST(REPLACE(Seconds, ',', '') AS INTEGER) / 86400), 2, '0'),
        ' ',
        TO_CHAR(
            TIMESTAMP '1970-01-01 00:00:00' + INTERVAL '1' SECOND * MOD(TRY_CAST(REPLACE(Seconds, ',', '') AS INTEGER), 86400),
            'HH24:MI:SS'
        )
    ) AS Converted
FROM Ex;

关键步骤说明 #

1. 计算完整天数：FLOOR(Seconds / 86400)，86400是一天的总秒数（24×60×60），FLOOR取整数部分得到完整天数。
2. 天数补零对齐：LPAD(..., 2, '0')确保天数显示为两位格式（如00而非0）。
3. 计算当日剩余秒数：MOD(Seconds, 86400)得到扣除完整天数后的剩余秒数。
4. 格式化为时分秒：把剩余秒数转换为时间对象，再通过TO_CHAR/TO_VARCHAR输出HH24:MI:SS格式。

结果验证 #

代入示例数据后：

• 4,061,796秒 → 47天 + 996秒（00:16:36）→ 47 00:16:36
• 2秒 → 0天 + 2秒 → 00 00:00:02

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