PostgreSQL中修正比赛时长（mm:ss）格式异常的方法

核心思路 #

把格式混乱的varchar时长统一转换为可计算的数值（总秒数）或interval类型，之后就能轻松实现排序、求平均等统计操作。

方案1：临时转换用于统计（不修改原表） #

如果不想改动原表结构，可以在查询时实时转换格式：

1.1 转换为总秒数（推荐，便于数值计算） #

用CASE语句判断格式，拆分字符串计算总秒数：

SELECT
  player_name,
  -- 计算总秒数
  CASE
    -- 处理hh:mm:ss格式（含2个冒号）
    WHEN play_time LIKE '%:%:%' THEN
      (split_part(play_time, ':', 1)::INT * 3600) +
      (split_part(play_time, ':', 2)::INT * 60) +
      split_part(play_time, ':', 3)::INT
    -- 处理mm:ss格式（仅1个冒号）
    ELSE
      (split_part(play_time, ':', 1)::INT * 60) +
      split_part(play_time, ':', 2)::INT
  END AS play_seconds,
  -- 可选：转换回mm:ss格式展示
  TO_CHAR(INTERVAL '1 second' * (
    CASE
      WHEN play_time LIKE '%:%:%' THEN
        (split_part(play_time, ':', 1)::INT * 3600) +
        (split_part(play_time, ':', 2)::INT * 60) +
        split_part(play_time, ':', 3)::INT
      ELSE
        (split_part(play_time, ':', 1)::INT * 60) +
        split_part(play_time, ':', 2)::INT
    END
  ), 'MI:SS') AS formatted_play_time
FROM boxscore;

1.2 基于转换后的秒数实现统计 #

找出出场时长最长的球员

SELECT
  player_name,
  play_seconds,
  TO_CHAR(INTERVAL '1 second' * play_seconds, 'MI:SS') AS play_time
FROM (
  SELECT
    player_name,
    CASE
      WHEN play_time LIKE '%:%:%' THEN
        (split_part(play_time, ':', 1)::INT * 3600) +
        (split_part(play_time, ':', 2)::INT * 60) +
        split_part(play_time, ':', 3)::INT
      ELSE
        (split_part(play_time, ':', 1)::INT * 60) +
        split_part(play_time, ':', 2)::INT
    END AS play_seconds
  FROM boxscore
) t
ORDER BY play_seconds DESC
LIMIT 1;

计算每位球员的平均出场时长

SELECT
  player_name,
  ROUND(AVG(play_seconds)) AS avg_seconds,
  TO_CHAR(INTERVAL '1 second' * ROUND(AVG(play_seconds)), 'MI:SS') AS avg_play_time
FROM (
  SELECT
    player_name,
    CASE
      WHEN play_time LIKE '%:%:%' THEN
        (split_part(play_time, ':', 1)::INT * 3600) +
        (split_part(play_time, ':', 2)::INT * 60) +
        split_part(play_time, ':', 3)::INT
      ELSE
        (split_part(play_time, ':', 1)::INT * 60) +
        split_part(play_time, ':', 2)::INT
    END AS play_seconds
  FROM boxscore
) t
GROUP BY player_name;

方案2：永久修正表结构（更高效） #

如果需要频繁统计，建议直接添加一个数值类型字段存储总秒数，后续操作更高效：

2.1 添加并更新总秒数字段 #

-- 添加INT类型的总秒数字段
ALTER TABLE boxscore ADD COLUMN play_seconds INT;

-- 批量更新数据到新字段
UPDATE boxscore
SET play_seconds = CASE
  WHEN play_time LIKE '%:%:%' THEN
    (split_part(play_time, ':', 1)::INT * 3600) +
    (split_part(play_time, ':', 2)::INT * 60) +
    split_part(play_time, ':', 3)::INT
  ELSE
    (split_part(play_time, ':', 1)::INT * 60) +
    split_part(play_time, ':', 2)::INT
END;

2.2 后续统计操作示例 #

有了play_seconds字段后，统计会非常简洁：

-- 最长出场时长
SELECT player_name, play_seconds, TO_CHAR(INTERVAL '1s' * play_seconds, 'MI:SS') AS play_time
FROM boxscore
ORDER BY play_seconds DESC
LIMIT 1;

-- 平均出场时长
SELECT player_name, ROUND(AVG(play_seconds)) AS avg_seconds, TO_CHAR(INTERVAL '1s' * ROUND(AVG(play_seconds)), 'MI:SS') AS avg_play_time
FROM boxscore
GROUP BY player_name;

方案3：转换为Interval类型（另一种可选方式） #

也可以将时长统一转换为PostgreSQL的INTERVAL类型，适合直接处理时间格式的统计：

SELECT
  player_name,
  CASE
    -- 对mm:ss格式补上前缀00:，统一为hh:mm:ss后转成interval
    WHEN play_time NOT LIKE '%:%:%' THEN ('00:' || play_time)::INTERVAL
    ELSE play_time::INTERVAL
  END AS play_interval
FROM boxscore;

基于interval的统计示例：

-- 最长出场时长
SELECT player_name, MAX(play_interval) AS max_play_time
FROM (
  SELECT
    player_name,
    CASE
      WHEN play_time NOT LIKE '%:%:%' THEN ('00:' || play_time)::INTERVAL
      ELSE play_time::INTERVAL
    END AS play_interval
  FROM boxscore
) t
GROUP BY player_name;

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