如何在Redshift中准确计算年龄？关于365与365.25计算差异的疑问

测试的SQL语句 #

SELECT FLOOR(DATEDIFF(day, '2022-01-01', '2023-01-01')/365) as age --1 (this seems right)
SELECT FLOOR(DATEDIFF(day, '2022-01-01', '2023-01-01')/365.25) as age --0 (this seems to undercount)
SELECT FLOOR(DATEDIFF(day, '2022-01-01', '2023-01-02')/365.25) as age --1 (this seems right)
SELECT FLOOR(DATEDIFF(day, '2022-01-01', '2080-01-01')/365) as age --58 (this seems right)
SELECT FLOOR(DATEDIFF(day, '2022-01-01', '2080-01-01')/365.25) as age --57 (this seems to undercount)
SELECT FLOOR(DATEDIFF(day, '2022-01-01', '2080-01-02')/365.25) as age --58 (this seems right)

问题解答 #

1. 除以365.25生日当天少统计年龄的现象是否合理？ #

这种现象是合理的，本质是纯数学计算逻辑导致的：

• 以第一个测试为例，2022-01-01到2023-01-01实际为365天，365除以365.25的结果约为0.9993，经过FLOOR函数取整后会被截断为0；而到2023-01-02是366天，366/365.25≈1.002，取整后为1。
• 365.25是用来近似平均年天数（考虑闰年）的数值，但这种「总天数除以平均年数再取整」的逻辑，和日常按「是否过了生日」计算年龄的规则不匹配。日常年龄计算按周年数判断，而非纯天数的平均折算，所以会出现生日当天「少统计」的错觉，但从数学计算逻辑来说，结果完全符合预期。

2. Redshift是否会自动处理闰年？ #

Redshift的DATEDIFF(day, start_date, end_date)函数会准确计算两个日期之间的实际天数，自动识别并处理闰年：

• 比如2020年（闰年）的2月28日到3月1日，DATEDIFF会返回2天；而非闰年的2021年同时间段，返回1天。
• 但需要注意：DATEDIFF只负责计算实际天数，不会直接完成年龄计算——年龄需要结合「生日是否已过」的逻辑判断，而非简单用天数除以固定数值。

更准确的年龄计算方式 #

如果要精准计算符合日常规则的年龄（过了生日才算满岁），可以用以下逻辑：

SELECT 
  CASE 
    WHEN DATE_PART(month, end_date) > DATE_PART(month, start_date) 
         OR (DATE_PART(month, end_date) = DATE_PART(month, start_date) 
             AND DATE_PART(day, end_date) >= DATE_PART(day, start_date))
    THEN DATE_PART(year, end_date) - DATE_PART(year, start_date)
    ELSE DATE_PART(year, end_date) - DATE_PART(year, start_date) - 1
  END AS age
FROM (
  SELECT '2022-01-01'::DATE AS start_date, '2023-01-01'::DATE AS end_date
) t;

这种方式不管是否遇到闰年，都能准确判断生日是否已过，返回正确的年龄。

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