Postgres 16

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

create table holidays(time_range) as values
 ('("2024-12-27 00:00:00","2025-02-01 00:00:00")'::tsrange)
,('("2025-05-01 00:00:00","2025-05-05 00:00:00")'::tsrange)
,('("2025-05-08 00:00:00","2025-05-20 00:00:00")'::tsrange);

SELECT time_range FROM holidays 
WHERE time_range && tsrange('2024-12-27', '2025-05-20', '[)');

SELECT 3




time_range


("2024-12-27 00:00:00","2025-02-01 00:00:00")

("2025-05-01 00:00:00","2025-05-05 00:00:00")

("2025-05-08 00:00:00","2025-05-20 00:00:00")



SELECT 3


SELECT date_part('year',holidate) "year", count(distinct holidate)
FROM holidays CROSS JOIN
generate_series( +(not lower_inc(time_range))::int+lower(time_range)::date
                ,-(not upper_inc(time_range))::int+upper(time_range)::date
                ,'1 day')as a(holidate)
WHERE holidate <@ tstzrange('2024-12-27', '2025-05-20', '[)')
GROUP BY "year";


year
count


2024
4

2025
45



SELECT 2


SELECT dense_rank()over(order by time_range) which_range_is_this,
       row_number()over(ORDER BY holidate) date_no,
       row_number()over(partition by extract(year from holidate)
                        ORDER BY holidate) no_in_year,
       holidate::date
FROM holidays CROSS JOIN
generate_series( +(not lower_inc(time_range))::int+lower(time_range)::date
                ,-(not upper_inc(time_range))::int+upper(time_range)::date
                ,'1 day')as a(holidate)
WHERE holidate <@ tstzrange('2024-12-27', '2025-05-20', '[)')
ORDER BY holidate;


which_range_is_this
date_no
no_in_year
holidate


1
1
1
2024-12-28

1
2
2
2024-12-29

1
3
3
2024-12-30

1
4
4
2024-12-31

1
5
1
2025-01-01

1
6
2
2025-01-02

1
7
3
2025-01-03

1
8
4
2025-01-04

1
9
5
2025-01-05

1
10
6
2025-01-06

1
11
7
2025-01-07

1
12
8
2025-01-08

1
13
9
2025-01-09

1
14
10
2025-01-10

1
15
11
2025-01-11

1
16
12
2025-01-12

1
17
13
2025-01-13

1
18
14
2025-01-14

1
19
15
2025-01-15

1
20
16
2025-01-16

1
21
17
2025-01-17

1
22
18
2025-01-18

1
23
19
2025-01-19

1
24
20
2025-01-20

1
25
21
2025-01-21

1
26
22
2025-01-22

1
27
23
2025-01-23

1
28
24
2025-01-24

1
29
25
2025-01-25

1
30
26
2025-01-26

1
31
27
2025-01-27

1
32
28
2025-01-28

1
33
29
2025-01-29

1
34
30
2025-01-30

1
35
31
2025-01-31

2
36
32
2025-05-02

2
37
33
2025-05-03

2
38
34
2025-05-04

3
39
35
2025-05-09

3
40
36
2025-05-10

3
41
37
2025-05-11

3
42
38
2025-05-12

3
43
39
2025-05-13

3
44
40
2025-05-14

3
45
41
2025-05-15

3
46
42
2025-05-16

3
47
43
2025-05-17

3
48
44
2025-05-18

3
49
45
2025-05-19



SELECT 49


alter table holidays add constraint non_overlapping_ranges 
  exclude using gist(time_range with &&);

ALTER TABLE

which_range_is_this	date_no	no_in_year	holidate
1	1	1	2024-12-28
1	2	2	2024-12-29
1	3	3	2024-12-30
1	4	4	2024-12-31
1	5	1	2025-01-01
1	6	2	2025-01-02
1	7	3	2025-01-03
1	8	4	2025-01-04
1	9	5	2025-01-05
1	10	6	2025-01-06
1	11	7	2025-01-07
1	12	8	2025-01-08
1	13	9	2025-01-09
1	14	10	2025-01-10
1	15	11	2025-01-11
1	16	12	2025-01-12
1	17	13	2025-01-13
1	18	14	2025-01-14
1	19	15	2025-01-15
1	20	16	2025-01-16
1	21	17	2025-01-17
1	22	18	2025-01-18
1	23	19	2025-01-19
1	24	20	2025-01-20
1	25	21	2025-01-21
1	26	22	2025-01-22
1	27	23	2025-01-23
1	28	24	2025-01-24
1	29	25	2025-01-25
1	30	26	2025-01-26
1	31	27	2025-01-27
1	32	28	2025-01-28
1	33	29	2025-01-29
1	34	30	2025-01-30
1	35	31	2025-01-31
2	36	32	2025-05-02
2	37	33	2025-05-03
2	38	34	2025-05-04
3	39	35	2025-05-09
3	40	36	2025-05-10
3	41	37	2025-05-11
3	42	38	2025-05-12
3	43	39	2025-05-13
3	44	40	2025-05-14
3	45	41	2025-05-15
3	46	42	2025-05-16
3	47	43	2025-05-17
3	48	44	2025-05-18
3	49	45	2025-05-19