PostgreSQL如何计算包含结束日期的年份差并生成Age字段

错误原因 #

• 原有逻辑直接用天数差除以固定值365，没有考虑闰年带来的年度天数差异，同时整数除法会自动舍去小数部分，当天数差不足N*365时会直接向下取整，刚好在起始日期为1月1日的场景下容易出现少算1年的问题。
• 你示例中的2019-01-01到2021-12-31总天数为1095天，若数据库除法逻辑存在隐式取整规则，或者存在时间精度损耗，就会出现计算结果为2的偏差。

修正方案 #

方案1（PostgreSQL适配，最稳定） #

你当前用的::date是PostgreSQL的特有强转语法，可以直接用原生AGE()函数处理日期差值，结合特殊判定即可满足需求：

UPDATE animals
SET age = EXTRACT(YEAR FROM AGE(benchmarkdate::date, birthdate::date)) 
        + CASE 
            WHEN (EXTRACT(MONTH FROM birthdate::date) = 1 AND EXTRACT(DAY FROM birthdate::date) = 1) 
                 OR (benchmarkdate::date >= (DATE_TRUNC('year', benchmarkdate::date) + (birthdate::date - DATE_TRUNC('year', birthdate::date))))
            THEN 1 
            ELSE 0 
          END;

验证你的示例场景：AGE('2021-12-31'::date, '2019-01-01'::date)提取年份得到2，触发1月1日的判定加1，最终结果为3，符合预期。

方案2（通用跨数据库方案） #

不依赖数据库特有函数，兼容性更强，不同数据库仅需调整年/月/日的提取函数即可：

UPDATE animals
SET age = (YEAR(benchmarkdate) - YEAR(birthdate)) 
        + CASE 
            WHEN (MONTH(birthdate) = 1 AND DAY(birthdate) = 1) 
                 OR (MONTH(benchmarkdate) > MONTH(birthdate)) 
                 OR (MONTH(benchmarkdate) = MONTH(birthdate) AND DAY(benchmarkdate) >= DAY(birthdate))
            THEN 1 
            ELSE 0 
          END;

注：SQL Server用DATEPART(year, 字段)提取年份，PostgreSQL用EXTRACT(YEAR FROM 字段)，可根据实际使用的数据库调整对应语法。

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