Как вернуть только рабочее время из бронирований в PostgreSql?

Question

Выберите лучший ответ в разделе Как найти первое свободное время в таблице бронирования в PostgreSql

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

используется для поиска пропусков в расписании, начиная с указанной даты и часа (2012-11-17 8: в примере ниже). Он также находит субботу, воскресенье и праздничные дни. Государственные праздники определены в таблице

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

требует отдельного ряда в объединении для каждого свободного времени.

Какой лучший способ вернуть свободное время в рабочие дни и рабочее время ( 8:00 .. 18:00), начиная с указанной даты и часа?

Обновить

Выбор в ответ возвращает свободное время в 8:00 всегда. Как вернуть свободное время не раньше указанного часа начала в указанную дату начала, например, не ранее 2012-11-19 9:00, если час начала равен 9? Стартовый час может иметь только значения 8,9,10,11,12,13,14,15,16 или 17

Даже если 2012-11-19 8:00, если он свободен, он должен вернуть 2012-11-19 9:00. Он должен возвращаться в 8:00, только если в 2012-11-19 гг. В 9:00 нет свободного времени, а в последующие рабочие дни 8:00 - первое свободное время.

Я попытался исправить это, добавив 2012-11-19 9: в два места, как показано в запросе ниже, но этот запрос по-прежнему возвращает свободное время в 2012-11-19 8:00. Как это исправить, чтобы он возвращал свободное время в 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

Я попытался обновить ответ, но он возвращает неправильные данные. Полный тестовый пример:

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

Наблюдаемый первый ряд:

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

Ожидаемый:

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

как исправить?

Answer 1

Вы можете использовать функцию generate_series() для маскировки нерабочих часов:

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

Позвольте мне объяснить некоторые хитрые части:

• Вы не должны вставлять даты для Sat/ Sun в pyha стол, потому что вы можете использовать date_part('dow', d) функция. использование pyha стол только для государственных праздников. "dow" возвращает 0 или 6 для Sun или Sat соответственно.
• государственные праздники и суббота / солнце могут быть представлены в виде одного интервала (0..24). Будние дни должны быть представлены двумя интервалами (0..8) и (18..24), следовательно, unnest () и array[]
• Вы можете указать дату и длину начала в функции generate_series()

На основании вашего обновления к вопросу я добавил еще один when в case:

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

Идея состоит в том, чтобы произвести разные интервалы для даты начала (d::date = '2012-11-14'): (0..9) и (18..24)