How to return only runtime from reservation in PostgreSql?

Question

Choose the perfect answer in How to find your first free time in the booking table at PostgreSql

create table reservation (during tsrange, EXCLUDE USING gist (during WITH &&) ); 

used to search for gaps in the schedule, starting from a given date and time (2012-11-17 8: in the example below) It finds Saturday, Sunday and holidays. Public holidays are defined in the table

 create table pyha ( pyha date primary key) 

How to exclude weekends and holidays?

Hard coding spare time reserved for type request

 with gaps as ( select upper(during) as start, lead(lower(during),1,upper(during)) over (ORDER BY during) - upper(during) as gap from ( select during from reservation union all values ('(,2012-11-17 8:)'::tsrange), -- given date and hour from which to find free work time ('[2012-11-17 0:,2012-11-18 24:)'::tsrange), -- exclude saturday ('[2012-11-18 0:,2012-11-19 8:)'::tsrange), -- exclude sunday ('[2012-11-19 18:,2012-11-20 8:)'::tsrange), ('[2012-11-20 18:,2012-11-21 8:)'::tsrange), ('[2012-11-21 18:,2012-11-22 8:)'::tsrange), ('[2012-11-22 18:,2012-11-23 8:)'::tsrange), ('[2012-11-23 18:,2012-11-24 24:)'::tsrange), ('[2012-11-24 0:,2012-11-25 24:)'::tsrange), -- exclude saturday ('[2012-11-25 0:,2012-11-26 8:)'::tsrange) -- exclude sunday ) as x ) select * from gaps where gap > '0'::interval order by start 

a separate union line is required for each free time range.

What is the best way to return free time on business days and hours (8:00 .. 18:00), starting from a given date and time?

Update

A choice in the answer returns free time at 8:00. How to return free time not earlier than the specified start hour on the specified start date, for example, not earlier than 2012-11-19 9:00 if the initial hour is 9? The starting hour can only have values ​​8,9,10,11,12,13,14,15,16 or 17

Even if 2012-11-19 8:00, if free, he must return at 2012-11-19 9:00. He should return 8:00 only if in 2012-11-19 in his free time at 9:00 and 8:00 there will be no free time in the first working days.

I tried to fix this by adding 2012-11-19 9: in two places, as shown in the following query, but this query still returns free time at 2012-11-19 8:00. How to fix this so that he returns his free time in 2012-11-19 9:00?

 create table reservation (during tsrange, EXCLUDE USING gist (during WITH &&) ); create table pyha ( pyha date primary key); with gaps as ( select upper(during) as start, lead(lower(during),1,upper(during)) over (ORDER BY during) - upper(during) as gap from ( select during from reservation where upper(during)>= '2012-11-19 9:' union all values ('(,2012-11-19 9:)'::tsrange) union all select unnest(case when pyha is not null then array[tsrange(d, d + interval '1 day')] when date_part('dow', d) in (0, 6) then array[tsrange(d, d + interval '1 day')] else array[tsrange(d, d + interval '8 hours'), tsrange(d + interval '18 hours', d + interval '1 day')] end) from generate_series( '2012-11-19'::timestamp without time zone, '2012-11-19'::timestamp without time zone+ interval '3 month', interval '1 day' ) as s(d) left join pyha on pyha = d::date ) as x ) select start, date_part('epoch', gap) / (60*60) as hours from gaps where gap > '0'::interval order by start 

Update2

I tried an updated answer, but it returns incorrect data. Full test test:

 create temp table reservation ( during tsrange ) on commit drop; insert into reservation values( '[2012-11-19 11:00:00,2012-11-19 11:30:00)'::tsrange ); with gaps as ( select upper(during) as start, lead(lower(during),1,upper(during)) over (ORDER BY during) - upper(during) as gap from ( select during from reservation union all select unnest(case when pyha is not null then array[tsrange(d, d + interval '1 day')] when date_part('dow', d) in (0, 6) then array[tsrange(d, d + interval '1 day')] when d::date = DATE'2012-11-19' then array[ tsrange(d, '2012-11-19 12:'), -- must return starting at 12:00 tsrange(d + interval '18 hours', d + interval '1 day')] else array[tsrange(d, d + interval '8 hours'), tsrange(d + interval '18 hours', d + interval '1 day')] end) from generate_series( DATE'2012-11-19'::timestamp without time zone, DATE'2012-11-19'::timestamp without time zone+ interval '3 month', interval '1 day' ) as s(d) left join pyha on pyha = d::date ) as x ) select start, date_part('epoch', gap) / (60*60) as tunde from gaps where gap > '0'::interval order by start 

Observed first line:

 "2012-11-19 11:30:00" 

Expected

:

 "2012-11-19 12:00:00" 

how to fix?

Answer 1

You can use the generate_series () function to mask non-working hours:

 with gaps as ( select upper(during) as start, lead(lower(during),1,upper(during)) over (ORDER BY during) - upper(during) as gap from ( select during from reservation union all select unnest(case when pyha is not null then array[tsrange(d, d + interval '1 day')] when date_part('dow', d) in (0, 6) then array[tsrange(d, d + interval '1 day')] when d::date = '2012-11-14' then array[tsrange(d, d + interval '9 hours'), tsrange(d + interval '18 hours', d + interval '1 day')] else array[tsrange(d, d + interval '8 hours'), tsrange(d + interval '18 hours', d + interval '1 day')] end) from generate_series( '2012-11-14'::timestamp without time zone, '2012-11-14'::timestamp without time zone + interval '2 week', interval '1 day' ) as s(d) left join pyha on pyha = d::date ) as x ) select * from gaps where gap > '0'::interval order by start 

Let me explain some tricky parts:

• You do not need to insert dates for sat / sun in the pyha table, since you can use the date_part('dow', d) function. Use the pyha table for holidays only. 'dow' returns 0 or 6 for Sun or Sat respectively.
• public holidays and sat / sun can be represented as a single interval (0..24). The days of the week should be represented by two intervals (0..8) and (18..24), therefore, unsest () and array []
• you can specify the date and date length in the generate_series () function

Based on your update to the question, I added another when in case :

 when d::date = '2012-11-14' then array[tsrange(d, d + interval '9 hours'), tsrange(d + interval '18 hours', d + interval '1 day')] 

The idea is to produce different intervals (dates) for the start date ( d::date = '2012-11-14' ): (0..9) and (18..24)