PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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, '[)')
Tags:

4

Answers


  1. 0

    using this data:

    create table example (
  x int,
  ts tsrange);
INSERT INTO example VALUES
  (1, '[2024-12-27 00:00:00,2025-02-01 00:00:00)'),
  (2, '[2025-05-01 00:00:00,2025-05-05 00:00:00)'),
  (3, '[2025-05-08 00:00:00,2025-05-20 00:00:00)');

    You can write a query which gets all dates in the range, and then count them.

    WITH RECURSIVE days as (
  SELECT 
    x, 
    LOWER(ts) as t 
  FROM example 
  
  UNION ALL
  
  SELECT 
    x, 
    t+'1 day' 
  FROM days 
  where t < (SELECT UPPER(ts) FROM example where x=days.x)
  )
SELECT 
  extract(year from t), count(*)
FROM days
GROUP BY extract(year from t)
ORDER BY extract(year from t)
;

    see: DBFIDDLE

    output:

    year count
    2024 5
    2025 50

    or, when you want them per daterange: DBFIDDLE

    output:

    x extract count
    1 2024 5
    1 2025 32
    2 2025 5
    3 2025 13
    Login or Signup to reply.
  2. 0

    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)
    Login or Signup to reply.
  3. 0

    See example.
    Whether or not to include boundary dates in the calculation is an open question. For example (‘2024-03-14’, ‘2024-03-15’) =0? =1? or =2 ?

    Sample data

    create table holidays (id int, date_range tsrange);
insert into holidays values
 (1,'("2024-12-27 00:00:00","2025-02-01 00:00:00")' )
,(2,'("2025-05-01 00:00:00","2025-05-05 00:00:00")' )
,(3,'("2025-05-08 00:00:00","2025-05-20 00:00:00")' )
;

    Query

    with cte1 as(
  select *
    ,case when date_part('year',upper(date_range))<>date_part('year',lower(date_range)) 
         then 1
     else 0
     end overyear
  from holidays
)
,cte2 as(
select * 
  ,lower(date_range) l
  ,upper(date_range) u
  ,date_part('day',upper(date_range)- lower(date_range)) d
  ,case when n=0 then date_part('year',lower(date_range))
   else date_part('year',upper(date_range))
   end yy
  ,case when overyear>0 and n=0 then
           date_part('day',date_trunc('year',upper(date_range))-lower(date_range))
        when overyear>0 and n=1 then 
           date_part('day',upper(date_range)-date_trunc('year',upper(date_range)))
   else date_part('day',upper(date_range)- lower(date_range))
   end lng
from cte1
left join (values (0),(1))t(n)
  on n<=overyear
)
--select * from cte2;
select yy,sum(lng)lng
from cte2
group by yy
order by yy
;

    Output

    yy lng
    2024 5
    2025 47

    Subquery result is

    id date_range overyear n l u d yy lng
    1 ("2024-12-27 00:00:00","2025-02-01 00:00:00") 1 0 2024-12-27 00:00:00 2025-02-01 00:00:00 36 2024 5
    1 ("2024-12-27 00:00:00","2025-02-01 00:00:00") 1 1 2024-12-27 00:00:00 2025-02-01 00:00:00 36 2025 31
    2 ("2025-05-01 00:00:00","2025-05-05 00:00:00") 0 0 2025-05-01 00:00:00 2025-05-05 00:00:00 4 2025 4
    3 ("2025-05-08 00:00:00","2025-05-20 00:00:00") 0 0 2025-05-08 00:00:00 2025-05-20 00:00:00 12 2025 12
    Login or Signup to reply.
  4. 0

    select sum(datediff(d,fromadate,todate)) from table which has fromdate todate extracted from tsrange

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search