如何将ISO 8601时长格式转为小时数十进制值（SQL实现）

嘿，这个需求我之前帮不少人解决过，把ISO 8601格式的时长（像PT8H0M这种）转成十进制小时其实挺 straightforward 的，核心就是把小时和分钟的数值提取出来，再做个简单的算术计算就行。

先给你理清楚逻辑：ISO 8601的时长格式是PT[小时数]H[分钟数]M，所以我们需要：

1. 去掉开头固定的PT前缀
2. 提取出H前面的数字作为小时数
3. 提取出H和M之间的数字作为分钟数
4. 用「小时数 + 分钟数/60」得到最终的十进制小时值

下面针对常用的几种数据库，给你具体的SELECT语句，你可以根据自己用的数据库直接套用：

1. MySQL/MariaDB #

用REPLACE、SUBSTRING_INDEX这些字符串函数就能搞定，假设你的字段叫duration，表名是your_table：

SELECT
  CAST(SUBSTRING_INDEX(REPLACE(duration, 'PT', ''), 'H', 1) AS DECIMAL(5,2)) +
  CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(duration, 'H', -1), 'M', 1) AS DECIMAL(5,2)) / 60 AS decimal_hours
FROM your_table;

举个例子，PT7H30M经过处理后，小时数是7，分钟数是30，30/60=0.5，加起来就是7.5，完美符合你的需求。

如果你的数据里存在只有小时（比如PT5H）或者只有分钟（比如PT20M）的情况，可以稍微改一下语句，避免转换报错：

SELECT
  COALESCE(CAST(CASE WHEN duration LIKE '%H%' THEN SUBSTRING_INDEX(REPLACE(duration, 'PT', ''), 'H', 1) ELSE '0' END AS DECIMAL(5,2)), 0) +
  COALESCE(CAST(CASE WHEN duration LIKE '%M%' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(duration, 'H', -1), 'M', 1) ELSE '0' END AS DECIMAL(5,2)), 0) / 60 AS decimal_hours
FROM your_table;

2. PostgreSQL #

PostgreSQL的split_part函数处理这种分割字符串的场景特别顺手：

SELECT
  CAST(split_part(split_part(duration, 'PT', 2), 'H', 1) AS NUMERIC) +
  CAST(split_part(split_part(duration, 'H', 2), 'M', 1) AS NUMERIC) / 60 AS decimal_hours
FROM your_table;

3. SQL Server #

用CHARINDEX定位字符位置，再用SUBSTRING截取：

SELECT
  CAST(SUBSTRING(duration, 3, CHARINDEX('H', duration) - 3) AS DECIMAL(5,2)) +
  CAST(SUBSTRING(duration, CHARINDEX('H', duration) + 1, CHARINDEX('M', duration) - CHARINDEX('H', duration) - 1) AS DECIMAL(5,2)) / 60 AS decimal_hours
FROM your_table;

你把这些语句里的表名和字段名换成你自己的，跑一下查询就能得到你想要的8.0、7.5、1.0这些结果啦。

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