EN VI

Is it possible with SQL to count all dateranges per year?

I select some dateranges with tsrange that includes a specific year.

I recieve an output like this:

  { time_range: '("2024-12-27 00:00:00","2025-02-01 00:00:00")' },
  { time_range: '("2025-05-01 00:00:00","2025-05-05 00:00:00")' },
  { time_range: '("2025-05-08 00:00:00","2025-05-20 00:00:00")' }

can I count all days grouped by year ?

currentcode:

SELECT time_range FROM holidays WHERE time_range && tsrange($1, $2, '[)')

Solution:

You don't seem to want a tsrange but rather a daterange. Otherwise I've kept your sample data. The series of CTE clauses are just to make it easier to read - you might want to merge some steps.

CREATE TEMP TABLE src (
    id            int PRIMARY KEY
    , event_range daterange NOT NULL
);

INSERT INTO src VALUES
    ( 1, '(2024-12-27,2025-02-01)' )
  , ( 2, '(2025-05-01,2025-05-05)' )
  , ( 3, '(2025-05-08,2025-05-20)' )
;

WITH target_years (y) AS (
    VALUES (2024), (2025), (2026)
)
, year_ranges (y, y_r) AS (
    SELECT y, daterange( make_date(y, 1, 1), make_date(y+1, 1, 1), '[)' )
    FROM target_years
)
, overlapping_ranges (y, o_r) AS (
    SELECT
        year_ranges.y
      , year_ranges.y_r * src.event_range AS o_r
    FROM
        year_ranges
        JOIN src    ON year_ranges.y_r && src.event_range
)
, range_day_counts AS (
    SELECT
        y
        , upper(o_r) - lower(o_r) AS days
    FROM
        overlapping_ranges
)
SELECT y, sum(days) AS tot_days
FROM range_day_counts
GROUP BY y
ORDER BY y
;

gives

  y   | tot_days 
------+----------
 2024 |        4
 2025 |       45
(2 rows)