能否用SQL按年份统计tsrange日期范围的天数？

完全可以按年份统计总天数，核心是把跨年度的时间范围拆分成分属不同年份的子区间，分别计算每个子区间的天数后再按年份汇总。

优化后的SQL查询 #

直接用下面的语句就能实现需求，会自动处理跨年度的区间：

WITH split_ranges AS (
    SELECT
        generate_series(
            date_trunc('year', lower(time_range))::timestamp,
            date_trunc('year', upper(time_range))::timestamp,
            interval '1 year'
        ) AS year_start,
        time_range
    FROM holidays
    WHERE time_range && tsrange($1, $2, '[)')
),
yearly_intervals AS (
    SELECT
        extract(year FROM year_start) AS year,
        tsrange(
            greatest(lower(time_range), year_start),
            least(upper(time_range), year_start + interval '1 year'),
            '[)'
        ) AS yearly_range
    FROM split_ranges
)
SELECT
    year,
    sum((upper(yearly_range) - lower(yearly_range))::int) AS total_days
FROM yearly_intervals
GROUP BY year
ORDER BY year;

逻辑拆解 #

• split_ranges：生成每个时间范围覆盖的所有年度起始点，为拆分跨年度区间做准备。
• yearly_intervals：把原时间范围和对应年度的起止时间取交集，得到每个年度内的有效子区间。
• 最终统计：计算每个子区间的天数并按年份求和，(upper - lower)::int直接得到两个时间戳之间的天数差。

你的示例数据计算结果 #

针对你给出的三个时间范围：

1. ("2024-12-27 00:00:00","2025-02-01 00:00:00")
   • 2024年：5天（12-27到12-31）
   • 2025年：32天（1-1到2-1）
2. ("2025-05-01 00:00:00","2025-05-05 00:00:00")
   • 2025年：4天
3. ("2025-05-08 00:00:00","2025-05-20 00:00:00")
   • 2025年：12天

最终统计结果：

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