Tsrange вывод временных интервалов?

Question

[Viktoria Smirnova]

Имею таблицу с резервом комнаты:

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 09:30, 2010-01-01 10:30)');

Помогите с запросами: О количестве свободных интервалов для этой комнаты 30 мин, 45мин, 1 час и 1.30 минут в течение дня, с 09.00 до 18.00.

Вывод:

30 мин
1. 09.00 - 09.30
2. 10.30 - 11.00
3. 11.30. -12.00
....

45 мин
1. 10.30 - 11.15
2. 11.15 - 12.00
...

1 час
1. 10.30. - 11.30
2. 11.30 - 12.30
....

Answer 1

[ponaehal]

В принципе можно решить и "в лоб" но лень...

В порядке бреда:
Заполнить таблицу временными минимальными доступными временными интервалами в промежутке с 9 до 18 (создать расписание).
В вашем случае это будет 18 тридцатиминутных интервалов (всего 18 строк).
Далее, например, в случае резервирования на час, резервировал бы сразу 2 интервала.

А потом все сводится к банальному UNION ALL
SELECT <выводим интервал времени если при переводе часов в минуты остаток от деления на 30 равен 0>
UNION ALL
SELECT <выводим интервал времени если при переводе часов в минуты остаток от деления на 45 равен 0>
UNION ALL
SELECT <выводим интервал времени если при переводе часов в минуты остаток от деления на 60 равен 0>
UNION ALL
SELECT <выводим интервал времени если при переводе часов в минуты остаток от деления на 90 равен 0>

Comments

[Viktoria Smirnova]

мысль ясна, но не хотелось бы городить кучу таблиц, из расчета, что комнат много как и дней в году, неужели другого подхода, более изящного нет?

Answer 2

[Вячеслав Успенский]

Оно?

with
  all_ranges as (
    SELECT tsrange(generate_series, 
                   generate_series + interval '30' minute, 
                   '()') as range 
    FROM generate_series(current_date::timestamp + interval '9' hour,
                         current_date::timestamp + interval '18' hour, 
                         '30 minutes'))      
select rooms.id as room_id,
       concat(to_char(lower(ar.range), 'HH24:MI'), 
              ' - ', 
              to_char(upper(ar.range), 'HH24:MI')) as timerange
from all_ranges as ar
cross join rooms 
where not exists (select 1 
                  from reservations as rt
                  where rooms.id=rt.room_id and 
                        ar.range && rt.occurrence)
order by 1;

SQL Fiddle

Comments

[Viktoria Smirnova]

Вячеслав Успенский Вячеслав спасибо за помощь!
По логике зарезервированное время:

insert into reservations (room_id, occurrence) values
(1, '[2018-06-26 09:30, 2018-06-26 10:30]'),
(1, '[2018-06-26 16:00, 2018-06-26 17:00]');

не должно отображаться, но оно в поле timerange присутствует? Или я что-то пропускаю из вида?
В топике я указывала, возможно ли отобрать временные интервалы 30, 45 мин, 1 час - без учета резерва?

[Вячеслав Успенский]

Там же в запросе current_date стоит, поправьте на желаемую дату и будет вам счастье)

[Viktoria Smirnova]

Вячеслав Успенский Вячеслав, алгоритм запроса не учитывает один момент, если в таблице запись такая:

[2018-06-26 09:30, 2018-06-26 10:30]

то следующий любой интервал после 10:30 должен начаться с 10:30, так и происходит с получасовыми интервалами и в некоторых случаях с 45 минутными интервалами, но не с часовыми. Часовые начинаются с 11:00. Почему так происходит я понимаю. Но в плане рациональности использования времени это контрпродуктивно. Возможно ли алгоритм предусмотреть таким образом что бы любой интервал начинался с окончания зарезервированного? Большое спасибо за Вашу помощь.