PostgreSQL两位年格式字符串日期转DATE提取年份异常如何解决

问题根因 #

PostgreSQL 使用YY格式掩码解析两位年份时，默认遵循70年分界规则：00~69区间的两位年份会被识别为2000_{2069年，`70~99`区间会被识别为1970}1999年。你遇到的10/1/58被识别为2058年就是这个规则导致的。

解决方案 #

方案1：动态修正年份（通用场景，优先选择） #

如果你的数据同时包含20世纪和21世纪的日期，可以通过逻辑判断自动修正偏移的年份：

-- 修正后的年份提取逻辑
EXTRACT(YEAR FROM (
  CASE
    WHEN TO_DATE(release_date, 'MM/DD/YY') > CURRENT_DATE
    THEN TO_DATE(release_date, 'MM/DD/YY') - INTERVAL '100 years'
    ELSE TO_DATE(release_date, 'MM/DD/YY')
  END
)) AS release_year

对应的近10年筛选条件可以简化为：

WHERE
  (CASE
    WHEN TO_DATE(release_date, 'MM/DD/YY') > CURRENT_DATE
    THEN TO_DATE(release_date, 'MM/DD/YY') - INTERVAL '100 years'
    ELSE TO_DATE(release_date, 'MM/DD/YY')
  END) >= CURRENT_DATE - INTERVAL '10 years'

方案2：强制指定世纪（仅适用于所有日期均为19xx年的场景） #

如果确认业务中所有release_date都属于1900-1999年，可以直接修改格式掩码强制指定世纪前缀，性能更高：

EXTRACT(YEAR FROM TO_DATE(release_date, 'MM/DD/19YY'))

方案3：底层数据治理（根治方案） #

有条件的话建议直接将日期存储格式统一改为带4位年份的YYYY-MM-DD格式，或者直接将字段类型修改为DATE，从根源上消除两位年份的解析歧义。

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