Postgres中如何获取两个timestamptz间的所有涉及年份？

原通过generate_series按时间步长生成年份的方式，当两个时间戳间隔不足一年时，无法返回结束时间所在的年份。要实现不管间隔是否满一年，都返回所有覆盖年份的需求，可以直接基于年份数值范围生成序列，具体方案如下：

解决方案代码 #

直接提取两个时间戳的年份数值，生成从起始年份到结束年份的整数序列，再转换为字符串格式：

-- 针对间隔不足一年的场景，返回2022和2023
SELECT 
  generate_series(
    EXTRACT(YEAR FROM '2022-06-14 11:00:00.000+00'::timestamptz)::INT,
    EXTRACT(YEAR FROM '2023-06-13 11:00:00.000+00'::timestamptz)::INT
  )::TEXT AS year;

验证间隔满一年的场景，同样能正确返回两个年份：

SELECT 
  generate_series(
    EXTRACT(YEAR FROM '2022-06-14 11:00:00.000+00'::timestamptz)::INT,
    EXTRACT(YEAR FROM '2023-06-14 11:00:00.000+00'::timestamptz)::INT
  )::TEXT AS year;

原理说明 #

1. 提取年份数值：EXTRACT(YEAR FROM ts) 从timestamptz类型中直接提取对应的年份整数
2. 生成年份序列：generate_series(start_int, end_int) 生成起始年份到结束年份之间的所有整数，包含两端
3. 格式转换：将整数年份转换为TEXT类型，匹配原需求的字符串输出格式

这种方法完全基于年份的数值范围生成结果，不受两个时间点之间的具体时间间隔影响，只要结束时间的年份不小于起始年份，就能返回所有涉及的年份（比如起始为2022-12-31、结束为2023-01-01的场景，也会正确返回2022和2023）。

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