Postgres 15

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

--https://stackoverflow.com/q/75384771/5298879
create table test_table (id smallserial, date_slot daterange);
insert into test_table values 
 (1,'[2023-02-08,2023-02-15)'),
 (2,'[2023-02-20,2023-02-26)'),
 (3,'[2023-02-27,2023-03-29)'),
 (4,'[2023-02-13,2023-02-18)'),
 (5,'[2023-02-12,2023-02-19)');

CREATE TABLE



INSERT 0 5


SELECT * 
FROM test_table 
where '2023-02-14'::date <@ date_slot
and extract(isodow from lower(date_slot)
                        + (not lower_inc(date_slot))::int)
  +(  (upper(date_slot) - (not upper_inc(date_slot))::int)
     -(lower(date_slot) + (not lower_inc(date_slot))::int) ) < 6 ;


id
date_slot


4
[2023-02-13,2023-02-18)



SELECT 1


select *,
  lower_inc(date_slot),
  extract(isodow from lower(date_slot)
                      +(not lower_inc(date_slot))::int) "start isodow",
   (upper(date_slot)-(not upper_inc(date_slot))::int)
  -(lower(date_slot)+(not lower_inc(date_slot))::int) "span",
  extract(isodow from lower(date_slot)
                      +(not lower_inc(date_slot))::int)
  +(  (upper(date_slot)-(not upper_inc(date_slot))::int)
     -(lower(date_slot)+(not lower_inc(date_slot))::int) ) "reach",
  extract(isodow from lower(date_slot)
                      +(not lower_inc(date_slot))::int)
  +(  (upper(date_slot)-(not upper_inc(date_slot))::int)
     -(lower(date_slot)+(not lower_inc(date_slot))::int) ) <6 "doesn't overlap weekend"
from test_table;


id
date_slot
lower_inc
start isodow
span
reach
doesn't overlap weekend


1
[2023-02-08,2023-02-15)
t
3
6
9
f

2
[2023-02-20,2023-02-26)
t
1
5
6
f

3
[2023-02-27,2023-03-29)
t
1
29
30
f

4
[2023-02-13,2023-02-18)
t
1
4
5
t

5
[2023-02-12,2023-02-19)
t
7
6
13
f



SELECT 5


SELECT * 
FROM test_table 
where '2023-02-14'::date <@ date_slot
and not exists (
  select true
  from generate_series(
          lower(date_slot) + (not lower_inc(date_slot))::int,
          upper(date_slot) - (not upper_inc(date_slot))::int,
          '1 day'::interval) as alias(d)
  where extract(isodow from d) in (6,7) );


id
date_slot


4
[2023-02-13,2023-02-18)



SELECT 1


SELECT a.*, 
       d::date as date, 
       extract(isodow from d) as isodow
FROM test_table a
  , generate_series(
          lower(date_slot) + (not lower_inc(date_slot))::int,
          upper(date_slot) - (not upper_inc(date_slot))::int,
          '1 day'::interval) as alias(d);


id
date_slot
date
isodow


1
[2023-02-08,2023-02-15)
2023-02-08
3

1
[2023-02-08,2023-02-15)
2023-02-09
4

1
[2023-02-08,2023-02-15)
2023-02-10
5

1
[2023-02-08,2023-02-15)
2023-02-11
6

1
[2023-02-08,2023-02-15)
2023-02-12
7

1
[2023-02-08,2023-02-15)
2023-02-13
1

1
[2023-02-08,2023-02-15)
2023-02-14
2

2
[2023-02-20,2023-02-26)
2023-02-20
1

2
[2023-02-20,2023-02-26)
2023-02-21
2

2
[2023-02-20,2023-02-26)
2023-02-22
3

2
[2023-02-20,2023-02-26)
2023-02-23
4

2
[2023-02-20,2023-02-26)
2023-02-24
5

2
[2023-02-20,2023-02-26)
2023-02-25
6

3
[2023-02-27,2023-03-29)
2023-02-27
1

3
[2023-02-27,2023-03-29)
2023-02-28
2

3
[2023-02-27,2023-03-29)
2023-03-01
3

3
[2023-02-27,2023-03-29)
2023-03-02
4

3
[2023-02-27,2023-03-29)
2023-03-03
5

3
[2023-02-27,2023-03-29)
2023-03-04
6

3
[2023-02-27,2023-03-29)
2023-03-05
7

3
[2023-02-27,2023-03-29)
2023-03-06
1

3
[2023-02-27,2023-03-29)
2023-03-07
2

3
[2023-02-27,2023-03-29)
2023-03-08
3

3
[2023-02-27,2023-03-29)
2023-03-09
4

3
[2023-02-27,2023-03-29)
2023-03-10
5

3
[2023-02-27,2023-03-29)
2023-03-11
6

3
[2023-02-27,2023-03-29)
2023-03-12
7

3
[2023-02-27,2023-03-29)
2023-03-13
1

3
[2023-02-27,2023-03-29)
2023-03-14
2

3
[2023-02-27,2023-03-29)
2023-03-15
3

3
[2023-02-27,2023-03-29)
2023-03-16
4

3
[2023-02-27,2023-03-29)
2023-03-17
5

3
[2023-02-27,2023-03-29)
2023-03-18
6

3
[2023-02-27,2023-03-29)
2023-03-19
7

3
[2023-02-27,2023-03-29)
2023-03-20
1

3
[2023-02-27,2023-03-29)
2023-03-21
2

3
[2023-02-27,2023-03-29)
2023-03-22
3

3
[2023-02-27,2023-03-29)
2023-03-23
4

3
[2023-02-27,2023-03-29)
2023-03-24
5

3
[2023-02-27,2023-03-29)
2023-03-25
6

3
[2023-02-27,2023-03-29)
2023-03-26
7

3
[2023-02-27,2023-03-29)
2023-03-27
1

3
[2023-02-27,2023-03-29)
2023-03-28
2

4
[2023-02-13,2023-02-18)
2023-02-13
1

4
[2023-02-13,2023-02-18)
2023-02-14
2

4
[2023-02-13,2023-02-18)
2023-02-15
3

4
[2023-02-13,2023-02-18)
2023-02-16
4

4
[2023-02-13,2023-02-18)
2023-02-17
5

5
[2023-02-12,2023-02-19)
2023-02-12
7

5
[2023-02-12,2023-02-19)
2023-02-13
1

5
[2023-02-12,2023-02-19)
2023-02-14
2

5
[2023-02-12,2023-02-19)
2023-02-15
3

5
[2023-02-12,2023-02-19)
2023-02-16
4

5
[2023-02-12,2023-02-19)
2023-02-17
5

5
[2023-02-12,2023-02-19)
2023-02-18
6



SELECT 55

id	date_slot	lower_inc	start isodow	span	reach	doesn't overlap weekend
1	[2023-02-08,2023-02-15)	t	3	6	9	f
2	[2023-02-20,2023-02-26)	t	1	5	6	f
3	[2023-02-27,2023-03-29)	t	1	29	30	f
4	[2023-02-13,2023-02-18)	t	1	4	5	t
5	[2023-02-12,2023-02-19)	t	7	6	13	f

id	date_slot	date	isodow
1	[2023-02-08,2023-02-15)	2023-02-08	3
1	[2023-02-08,2023-02-15)	2023-02-09	4
1	[2023-02-08,2023-02-15)	2023-02-10	5
1	[2023-02-08,2023-02-15)	2023-02-11	6
1	[2023-02-08,2023-02-15)	2023-02-12	7
1	[2023-02-08,2023-02-15)	2023-02-13	1
1	[2023-02-08,2023-02-15)	2023-02-14	2
2	[2023-02-20,2023-02-26)	2023-02-20	1
2	[2023-02-20,2023-02-26)	2023-02-21	2
2	[2023-02-20,2023-02-26)	2023-02-22	3
2	[2023-02-20,2023-02-26)	2023-02-23	4
2	[2023-02-20,2023-02-26)	2023-02-24	5
2	[2023-02-20,2023-02-26)	2023-02-25	6
3	[2023-02-27,2023-03-29)	2023-02-27	1
3	[2023-02-27,2023-03-29)	2023-02-28	2
3	[2023-02-27,2023-03-29)	2023-03-01	3
3	[2023-02-27,2023-03-29)	2023-03-02	4
3	[2023-02-27,2023-03-29)	2023-03-03	5
3	[2023-02-27,2023-03-29)	2023-03-04	6
3	[2023-02-27,2023-03-29)	2023-03-05	7
3	[2023-02-27,2023-03-29)	2023-03-06	1
3	[2023-02-27,2023-03-29)	2023-03-07	2
3	[2023-02-27,2023-03-29)	2023-03-08	3
3	[2023-02-27,2023-03-29)	2023-03-09	4
3	[2023-02-27,2023-03-29)	2023-03-10	5
3	[2023-02-27,2023-03-29)	2023-03-11	6
3	[2023-02-27,2023-03-29)	2023-03-12	7
3	[2023-02-27,2023-03-29)	2023-03-13	1
3	[2023-02-27,2023-03-29)	2023-03-14	2
3	[2023-02-27,2023-03-29)	2023-03-15	3
3	[2023-02-27,2023-03-29)	2023-03-16	4
3	[2023-02-27,2023-03-29)	2023-03-17	5
3	[2023-02-27,2023-03-29)	2023-03-18	6
3	[2023-02-27,2023-03-29)	2023-03-19	7
3	[2023-02-27,2023-03-29)	2023-03-20	1
3	[2023-02-27,2023-03-29)	2023-03-21	2
3	[2023-02-27,2023-03-29)	2023-03-22	3
3	[2023-02-27,2023-03-29)	2023-03-23	4
3	[2023-02-27,2023-03-29)	2023-03-24	5
3	[2023-02-27,2023-03-29)	2023-03-25	6
3	[2023-02-27,2023-03-29)	2023-03-26	7
3	[2023-02-27,2023-03-29)	2023-03-27	1
3	[2023-02-27,2023-03-29)	2023-03-28	2
4	[2023-02-13,2023-02-18)	2023-02-13	1
4	[2023-02-13,2023-02-18)	2023-02-14	2
4	[2023-02-13,2023-02-18)	2023-02-15	3
4	[2023-02-13,2023-02-18)	2023-02-16	4
4	[2023-02-13,2023-02-18)	2023-02-17	5
5	[2023-02-12,2023-02-19)	2023-02-12	7
5	[2023-02-12,2023-02-19)	2023-02-13	1
5	[2023-02-12,2023-02-19)	2023-02-14	2
5	[2023-02-12,2023-02-19)	2023-02-15	3
5	[2023-02-12,2023-02-19)	2023-02-16	4
5	[2023-02-12,2023-02-19)	2023-02-17	5
5	[2023-02-12,2023-02-19)	2023-02-18	6