求助：SQL中将‘X Days X Hours X Minutes’格式转为总分钟数

针对格式如3 Days 18 Hours 51 Minutes的时长字符串，无需依赖固定位置提取数字，可通过关键词定位或正则匹配的方式，灵活提取天、小时、分钟的数值并计算总分钟数，避免单数字小时/分钟导致的提取偏差。

MySQL 实现 #

利用SUBSTRING_INDEX按关键词拆分字符串，提取对应数值：

SELECT 
  (
    -- 提取天数并转分钟（1天=1440分钟）
    CAST(SUBSTRING_INDEX(duration_str, ' Days', 1) AS UNSIGNED) * 1440 +
    -- 提取小时数并转分钟（1小时=60分钟）：先截到Hours前，再取最后一段数字
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(duration_str, ' Hours', 1), ' ', -1) AS UNSIGNED) * 60 +
    -- 提取分钟数：先截到Minutes前，再取最后一段数字
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(duration_str, ' Minutes', 1), ' ', -1) AS UNSIGNED)
  ) AS total_minutes
FROM your_table;

SQL Server 实现 #

方法1：通过字符定位提取 #

SELECT 
  (
    CAST(LEFT(duration_str, CHARINDEX(' Days', duration_str) - 1) AS INT) * 1440 +
    CAST(SUBSTRING(duration_str, CHARINDEX(' ', duration_str) + 1, CHARINDEX(' Hours', duration_str) - CHARINDEX(' ', duration_str) - 1) AS INT) * 60 +
    CAST(SUBSTRING(duration_str, CHARINDEX(' Hours', duration_str) + 7, CHARINDEX(' Minutes', duration_str) - CHARINDEX(' Hours', duration_str) - 7) AS INT)
  ) AS total_minutes
FROM your_table;

方法2：拆分字符串后匹配关键词 #

WITH split_parts AS (
  SELECT 
    duration_str,
    value AS part
  FROM your_table
  CROSS APPLY STRING_SPLIT(duration_str, ' ')
)
SELECT 
  (
    MAX(CASE WHEN part LIKE '%Days' THEN CAST(LEFT(part, LEN(part)-4) AS INT) END) * 1440 +
    MAX(CASE WHEN part LIKE '%Hours' THEN CAST(LEFT(part, LEN(part)-5) AS INT) END) * 60 +
    MAX(CASE WHEN part LIKE '%Minutes' THEN CAST(LEFT(part, LEN(part)-7) AS INT) END)
  ) AS total_minutes
FROM split_parts
GROUP BY duration_str;

PostgreSQL 实现 #

利用正则表达式regexp_match直接匹配每个数值段：

SELECT 
  (
    (regexp_match(duration_str, '(\d+) Days'))[1]::INT * 1440 +
    (regexp_match(duration_str, '(\d+) Hours'))[1]::INT * 60 +
    (regexp_match(duration_str, '(\d+) Minutes'))[1]::INT
  ) AS total_minutes
FROM your_table;

核心逻辑：通过关键词（Days/Hours/Minutes）或正则匹配定位数字，不依赖固定字符位置，适配单/多位数的小时、分钟场景。

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