Postgres 11

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

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE bookings
(
    "b_id" integer NOT NULL,
    "in" date,
    "out" date,
    CONSTRAINT bookings_pkey PRIMARY KEY ("b_id")
)
WITH (
    OIDS = FALSE
)
INSERT INTO bookings VALUES (1, '2018-11-01', '2018-11-30');
INSERT INTO bookings VALUES (2, '2018-12-25', '2019-01-10');
1 rows affected

1 rows affected

select * from bookings


b_id
in
out


1
2018-11-01
2018-11-30

2
2018-12-25
2019-01-10


select date_part('day', "out"::timestamp - "in"::timestamp) as t1 from bookings


t1


29

16


--primero genero la serie de dias por separado
SELECT generate_series("in"::timestamp, "out"::timestamp, '1 day'::interval) AS fechas_ocupadas 
FROM bookings


fechas_ocupadas


2018-11-01 00:00:00

2018-11-02 00:00:00

2018-11-03 00:00:00

2018-11-04 00:00:00

2018-11-05 00:00:00

2018-11-06 00:00:00

2018-11-07 00:00:00

2018-11-08 00:00:00

2018-11-09 00:00:00

2018-11-10 00:00:00

2018-11-11 00:00:00

2018-11-12 00:00:00

2018-11-13 00:00:00

2018-11-14 00:00:00

2018-11-15 00:00:00

2018-11-16 00:00:00

2018-11-17 00:00:00

2018-11-18 00:00:00

2018-11-19 00:00:00

2018-11-20 00:00:00

2018-11-21 00:00:00

2018-11-22 00:00:00

2018-11-23 00:00:00

2018-11-24 00:00:00

2018-11-25 00:00:00

2018-11-26 00:00:00

2018-11-27 00:00:00

2018-11-28 00:00:00

2018-11-29 00:00:00

2018-11-30 00:00:00

2018-12-25 00:00:00

2018-12-26 00:00:00

2018-12-27 00:00:00

2018-12-28 00:00:00

2018-12-29 00:00:00

2018-12-30 00:00:00

2018-12-31 00:00:00

2019-01-01 00:00:00

2019-01-02 00:00:00

2019-01-03 00:00:00

2019-01-04 00:00:00

2019-01-05 00:00:00

2019-01-06 00:00:00

2019-01-07 00:00:00

2019-01-08 00:00:00

2019-01-09 00:00:00

2019-01-10 00:00:00


--segundo creo una columna para cada parte de la fecha
select
date_part('year', fechas_ocupadas) as año, 
date_part('month', fechas_ocupadas) as mes,
date_part('day', fechas_ocupadas) as dia
from
(
SELECT generate_series("in"::timestamp, "out"::timestamp, '1 day'::interval) AS fechas_ocupadas 
FROM bookings
) as t1


año
mes
dia


2018
11
1

2018
11
2

2018
11
3

2018
11
4

2018
11
5

2018
11
6

2018
11
7

2018
11
8

2018
11
9

2018
11
10

2018
11
11

2018
11
12

2018
11
13

2018
11
14

2018
11
15

2018
11
16

2018
11
17

2018
11
18

2018
11
19

2018
11
20

2018
11
21

2018
11
22

2018
11
23

2018
11
24

2018
11
25

2018
11
26

2018
11
27

2018
11
28

2018
11
29

2018
11
30

2018
12
25

2018
12
26

2018
12
27

2018
12
28

2018
12
29

2018
12
30

2018
12
31

2019
1
1

2019
1
2

2019
1
3

2019
1
4

2019
1
5

2019
1
6

2019
1
7

2019
1
8

2019
1
9

2019
1
10


--tercero agrupo los resultados
select año, mes, count(*) as dias_de_ocupacion from
(
select
date_part('year', fechas_ocupadas) as año, 
date_part('month', fechas_ocupadas) as mes,
date_part('day', fechas_ocupadas) as dia
from
(
SELECT generate_series("in"::timestamp, "out"::timestamp, '1 day'::interval) AS fechas_ocupadas 
FROM bookings
) as t1
) as t2
group by año, mes
order by año desc, mes desc


año
mes
dias_de_ocupacion


2019
1
10

2018
12
7

2018
11
30

año	mes	dia
2018	11	1
2018	11	2
2018	11	3
2018	11	4
2018	11	5
2018	11	6
2018	11	7
2018	11	8
2018	11	9
2018	11	10
2018	11	11
2018	11	12
2018	11	13
2018	11	14
2018	11	15
2018	11	16
2018	11	17
2018	11	18
2018	11	19
2018	11	20
2018	11	21
2018	11	22
2018	11	23
2018	11	24
2018	11	25
2018	11	26
2018	11	27
2018	11	28
2018	11	29
2018	11	30
2018	12	25
2018	12	26
2018	12	27
2018	12	28
2018	12	29
2018	12	30
2018	12	31
2019	1	1
2019	1	2
2019	1	3
2019	1	4
2019	1	5
2019	1	6
2019	1	7
2019	1	8
2019	1	9
2019	1	10