Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();

version

PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

SELECT 1

create table example (
  x int,
  ts tsrange);

CREATE TABLE

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)');

INSERT 0 3

SELECT * FROM example;

x
ts

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")

SELECT 3

SELECT x, UPPER(ts)-LOWER(ts)
FROM example

x
?column?

1
36 days

2
4 days

3
12 days

SELECT 3

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 x,
  extract(year from t), count(*)
FROM days
GROUP BY x,extract(year from t)
ORDER BY x,extract(year from t)
;

x
extract
count

1
2024
5

1
2025
32

2
2025
5

3
2025
13

SELECT 4

x	ts
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")