在PostgreSQL中将字符串格式日期转换为日期类型并计算年龄

你遇到的报错和异常计算结果主要来自三个问题：

• 字段缺失值为字符串'None'而非SQL原生NULL，直接做类型转换会触发报错
• 带时区的ISO格式时间字符串直接CAST为DATE类型时，低年份值（如示例中的0133年、0193年）会被PostgreSQL错误解析，出现年份偏移，最终得到2210这类不符合预期的差值
• 两个DATE类型直接相减得到的是天数差，而非实际周岁年龄，即使日期解析正确结果也不符合需求

你可以直接使用下面的SQL语句完成计算，替换your_table_name为你的实际表名即可：

SELECT
  CASE
    WHEN birth = 'None' OR death = 'None' THEN 'unknown'
    ELSE EXTRACT(YEAR FROM AGE(death::timestamptz, birth::timestamptz))::INTEGER::VARCHAR
  END AS calculated_age
FROM your_table_name;

语句逻辑说明 #

• 首先判断两个日期字段任意为'None'时直接返回unknown，规避缺失值报错
• 先将字符串转换为timestamptz（带时区的时间戳）类型，可正确识别0133-01-30T00:53:28+00:53这类带时区的ISO格式时间，避免日期解析偏差
• 调用PostgreSQL内置AGE()函数计算两个时间的间隔，自动处理闰年、跨月等场景，得到准确的年龄间隔
• 用EXTRACT(YEAR FROM ...)提取间隔的年份值，即为周岁年龄

如果你的表中存在部分格式不规整的日期字符串，可以用TO_TIMESTAMP函数指定格式实现更稳定的解析，示例如下：

TO_TIMESTAMP(birth, 'YYYY-MM-DD"T"HH24:MI:SSOF')

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