如何将SQL查询返回的时间间隔转为ISO8601时长格式？

不同数据库的实现方式略有差异，以下是几种主流数据库的解决方案：

PostgreSQL #

PostgreSQL直接支持interval类型，可以通过提取时间差的各部分（时、分、秒），再拼接成ISO8601格式：

SELECT 'PT' ||
       CASE WHEN EXTRACT(HOUR FROM diff) > 0 THEN EXTRACT(HOUR FROM diff) || 'H' ELSE '' END ||
       CASE WHEN EXTRACT(MINUTE FROM diff) > 0 THEN EXTRACT(MINUTE FROM diff) || 'M' ELSE '' END ||
       CASE WHEN EXTRACT(SECOND FROM diff) > 0 THEN EXTRACT(SECOND FROM diff) || 'S' ELSE '' END AS iso_duration
FROM (
    SELECT max(ended_at) - min(started_at) AS diff
    FROM test_run
) t;

如果时间差仅为5秒，该查询会返回PT5S；若为1小时30分15秒，则返回PT1H30M15S。

MySQL #

MySQL可以通过TIMESTAMPDIFF函数拆分时间差的各部分，再拼接成目标格式：

SELECT CONCAT('PT',
       CASE WHEN hours > 0 THEN CONCAT(hours, 'H') ELSE '' END,
       CASE WHEN minutes > 0 THEN CONCAT(minutes, 'M') ELSE '' END,
       CASE WHEN seconds > 0 THEN CONCAT(seconds, 'S') ELSE '' END) AS iso_duration
FROM (
    SELECT 
        TIMESTAMPDIFF(HOUR, MIN(started_at), MAX(ended_at)) AS hours,
        MOD(TIMESTAMPDIFF(MINUTE, MIN(started_at), MAX(ended_at)), 60) AS minutes,
        MOD(TIMESTAMPDIFF(SECOND, MIN(started_at), MAX(ended_at)), 60) AS seconds
    FROM test_run
) t;

如果只需要精确到秒（忽略时、分的0值），也可以简化为：

SELECT CONCAT('PT', TIMESTAMPDIFF(SECOND, MIN(started_at), MAX(ended_at)), 'S') AS iso_duration
FROM test_run;

SQL Server #

SQL Server使用DATEDIFF提取时间差各部分，再拼接成ISO8601时长格式：

SELECT 'PT' +
       CASE WHEN hours > 0 THEN CAST(hours AS VARCHAR) + 'H' ELSE '' END +
       CASE WHEN minutes > 0 THEN CAST(minutes AS VARCHAR) + 'M' ELSE '' END +
       CASE WHEN seconds > 0 THEN CAST(seconds AS VARCHAR) + 'S' ELSE '' END AS iso_duration
FROM (
    SELECT 
        DATEDIFF(HOUR, MIN(started_at), MAX(ended_at)) AS hours,
        DATEDIFF(MINUTE, MIN(started_at), MAX(ended_at)) % 60 AS minutes,
        DATEDIFF(SECOND, MIN(started_at), MAX(ended_at)) % 60 AS seconds
    FROM test_run
) t;

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