在SQL中如何将字符串格式Unix时间转换为对应日序号

现有表结构 #

SQL表Table1包含2个字段：

• ID：行唯一标识
• UnixTimes：字符串类型，存储单个或多个逗号分隔的Unix时间戳，示例值：1632862800,1632949200

需求目标 #

将每行UnixTimes字段内的每个Unix时间戳单独转换为对应月份的日序号，多个转换结果仍用逗号分隔，最终输出格式如下：

1- 21,22
2- 22
3- 22,23
4- 21,22,23
5- 24,25

实现方案 #

不同数据库的字符串处理、时间转换函数存在差异，以下是主流数据库的实现示例：

MySQL 8.0+ 实现 #

SELECT 
  CONCAT(t.ID, '- ', GROUP_CONCAT(DAY(FROM_UNIXTIME(CAST(ts.value AS UNSIGNED))) ORDER BY ts.idx SEPARATOR ',')) AS result
FROM Table1 t
JOIN JSON_TABLE(
  CONCAT('["', REPLACE(t.UnixTimes, ',', '","'), '"]'),
  '$[*]' COLUMNS (
    idx FOR ORDINALITY,
    value VARCHAR(20) PATH '$'
  )
) ts
GROUP BY t.ID
ORDER BY t.ID;

如果是MySQL 8.0以下版本不支持JSON_TABLE，可通过辅助数字表配合SUBSTRING_INDEX完成字符串拆分逻辑。

PostgreSQL 实现 #

SELECT 
  CONCAT(t.ID, '- ', STRING_AGG(EXTRACT(DAY FROM TO_TIMESTAMP(ts::BIGINT))::VARCHAR, ',' ORDER BY ordinality)) AS result
FROM Table1 t,
UNNEST(STRING_TO_ARRAY(t.UnixTimes, ',')) WITH ORDINALITY AS a(ts)
GROUP BY t.ID
ORDER BY t.ID;

核心逻辑梳理 #

所有数据库的实现都遵循相同的三步逻辑：

1. 拆分UnixTimes字段的逗号分隔字符串，将每个时间戳拆分为独立行，同时保留原ID关联
2. 将单个Unix时间戳转换为日期类型，提取对应月份的日序号
3. 按原ID分组，把提取到的日序号重新拼接为逗号分隔的字符串，按要求格式化输出

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