Postgres check that repeatable event overlap with time slots-CodePudding

In general, I have something similar to the calendar.

In my database, I have repeatable events. To simplify work with them I generate time slots during which booking room will be reserved.

Table event

id                 long
room_uuid          varchar
start_date         timestamp
end_date           time_stamp
repeat_every_min   long
duration_min       long

And another table:

Table event_time_slot

id            long
event_id      long (fk)
start_date    timestamp
end_date      time_stamp

How it looks like with mock data:

Table event mock data

id                 1
room_uuid          267cb70a-6911-488c-aa9e-9deb506f785b
start_date         "2023-01-05 10:00:00"
end_date           "2023-01-05 10:57:00"
repeat_every_min   15
duration_min       10

As result in the table event_time_slot I will have next records:

id            1
event_id      1
start_date    "2023-01-05 10:00:00"
end_date      "2023-01-05 10:10:00"
____________________________________
id            2
event_id      1
start_date    "2023-01-05 10:15:00"
end_date      "2023-01-05 10:20:00"
____________________________________
id            3
event_id      1
start_date    "2023-01-05 10:30:00"
end_date      "2023-01-05 10:35:00"
____________________________________
id            4
event_id      1
start_date    "2023-01-05 10:45:00"
end_date      "2023-01-05 10:55:00"

Basically, I will generate time slots while ((startTime N * duration) repeatEveryMin) < endTime

My current flow to check will 2 repeatable events conflict or not is quite simple: I generate time slots for event, and I do

select from event_time_slot ts
join event_time_slot its on its.event_id = ts_id
where 

//condition that any of the saved slots overlaps with first generated slots
(its.start_date < (*endTime*) AND  its.start_date > (*startTime*))
or
//condition that any of the saved slots overlaps (equal) with first generated slots
(its.start_date = (*endTime*) AND  its.start_date = (*startTime*))

The problem is that it forces me to generate a lot of the time slots to execute this query. Moreover, if I have event with 100 time_slots -> I will need to check that any of the previously saved event time slots do not overlap with 100 which I am going to save.

My question is:

Is in the Postgres any functionality, which can simplify working with repeatable events?
Is there any other technology, which solves this problem?

What I have tried:

To generate time slots for the event. The problem is that query is too complex and if I will have more than 5000 time slots for the 1 event -> I will need to do multiple queries to the DB, because I will receive memory error in my app.

Expecting to receive a feedback or a technology how Postgres can simplify current flow.

My primary question is - does Postgres have any functionality, to remove work with time slots at all?

For example - I pass startDate endDate repeatInterval to the query and SQL shows me overlapping events.

I want to avoid creating condition for every time_slot from event for which I want to check this

CodePudding user response：

This query generates 4 time slots:

SELECT  
    tsrange(ts, ts   INTERVAL '10 MINUTE', '[)')
FROM    generate_series(
    '2023-01-05 10:00:00'::timestamp
    , '2023-01-05 10:57:00'::timestamp
    , INTERVAL '15 MINUTE') g(ts)
WHERE ts::time BETWEEN '09:00' AND '17:55' -- business hours
AND EXTRACT(DOW FROM ts) BETWEEN 1 AND 5 -- Monday to Friday
-- other conditions

Check the manual for all options you have with ranges, including the very powerful constraints to avoid overlapping events.