Timeslots in POSTGRESQL?

Question

PostgreSQL

At the output in the tsrange field, I have two time intervals:

["2010-01-01 09:00:00","2010-01-01 09:30:00")
["2010-01-01 10:30:00","2010-01-01 18:00 :00")

The request is this:

WITH mins(lt) AS (
  /* get all lower bounds of intervals */
  SELECT lower(during)
  FROM reservation
  UNION
  /* get 18:00 for each day */
  SELECT date_trunc('day', lower(during)) + INTERVAL '18 hours'
  FROM reservation
), maxs(ut) AS (
  /* get all upper bounds of intervals */
  SELECT upper(during)
  FROM reservation
  UNION
  /* get 09:00 for each day */
  SELECT date_trunc('day', lower(during)) + INTERVAL '9 hours'
  FROM reservation
)
SELECT tsrange(ut, lt)  /* candidate for a free interval */
FROM mins
  JOIN maxs
    ON date_trunc('day', lt) = date_trunc('day', ut)
       AND ut < lt
/* exclude all such intervals that overlap an entry*/
WHERE NOT EXISTS (SELECT 1 FROM reservation
WHERE during && tsrange(ut, lt, '()'))
ORDER BY ut;

The table is like this:

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

Question, guys, please help with the division of these segments into 15 minute segments. Thank you in advance.

Answer 1

generate_series(interval start date, interval end date, interval '15 minutes')t
to help you)
you can join to your table and so

select t.tt, r.*
from reservation r
left join lateral generate_series(lower(r.during), upper(r.during), interval '15 min')t(tt) on true