V
V
Viktoria Smirnova2018-06-30 23:54:32
PostgreSQL
Viktoria Smirnova, 2018-06-30 23:54:32

Tsrange output time intervals (vol2)?

The question is in the request for the number of free slots for an occupied room.
In response to the first topic , it was proposed to follow the path of dividing the day into time intervals (15,30,45 ... min), and in the case of a range overlay, exclusion of these intervals. This led to the fact that the hour segments will always start from exactly 12.00, 13.00, 14.00 ..., which is counterproductive, because if the last reserve ended at 12.10. 50 minutes will be idle.
SO has suggested a different approach. To begin with, the entire free timing is determined, and then filtered by duration. The author of the topics is not strong in SQL at all.
Help with filtering.

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

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;

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question