Tsrange output of time intervals?

Question

PostgreSQL

I have a table with room reservation:

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

Help with requests: About the number of free intervals for this room 30 minutes, 45 minutes, 1 hour and 1.30 minutes during the day, from 09.00 to 18.00.
Conclusion:

30 min
1. 09.00 - 09.30
2. 10.30 - 11.00
3. 11.30. -12.00
....
45 min
1. 10.30 - 11.15
2. 11.15 - 12.00
...
1 hour
1. 10.30. - 11.30
2. 11.30 - 12.30
....

Answer 1

In principle, you can solve it "on the forehead" but too lazy ...
In the order of delirium:
Fill in the table with temporary minimum available time intervals in the interval from 9 to 18 (create a schedule).
In your case it will be 18 thirty minute intervals (18 lines in total).
Further, for example, in the case of a reservation for an hour, I would reserve 2 intervals at once.
And then it all comes down to the banal UNION ALL
SELECT <display the time interval if, when converting hours to minutes, the remainder of division by 30 is 0>
UNION ALL
SELECT <display the time interval if, when converting hours to minutes, the remainder of division by 45 is 0>
UNION ALL
SELECT <display the time interval if the remainder of division by 60 is 0 when converting hours to minutes>
UNION ALL
SELECT <display the time interval if the remainder of division by 90 is 0 when converting hours to minutes>

Answer 2

It?

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