How to avoid overlapping of dates in a mysql table

Question

I am making a movie theatre management system in Python using mySQL and I need to make it so that if a datetime range is to be inserted such that it overlaps with another show that an error would be brought up.

For example, if I have a certain movie at 8PM in hall 1, and if i tried to insert another movie at the same time, an error would be brought up.

I have made a composite key between start time and hall that if there is an exact match in the newly inputed time and hall that an error is brought up. I have another column for the time when the movie ends and I would like to know how to make it possible that no other movie can be inserted between the start and end, for this certain hall.

Table

This is the layout of the database

cur.execute(f"INSERT INTO SHOWS(movie_name, showtimings,showend, hall, price, pricevip, pricevipb, priceprem) VALUES('{movname}', '{showtimings}','{endtime}', {hall}, {price}, {pricevip}, {pricevipb}, {priceprem})")

You can refer to post: https://stackoverflow.com/questions/9044084/efficient-date-range-overlap-calculation — Brijesh Varsani, Nov 13 '22 at 19:13
MySQL have no RANGE datatype. You may solve your restriction with a trigger. — Akina, Nov 13 '22 at 20:58

score 0 · Answer 1 · answered Nov 13 '22 at 21:29

This can be done in a 3-step process:

Identify available time slots based on current schedule
Check if the new schedule fits in available time slots
Insert the new schedule if a time slot available

First, simplify the schema and sample data to focus on the main question (note that reserved schedules are inserted if the hall if not available 24 hours a day):

create table shows (
    movie_name varchar(100),
    start_time timestamp,
    end_time timestamp,
    hall_id  int
);

insert into shows values ('@reserve#1', '2022-11-18 00:00:00', '2022-11-18 10:30:00', 1);
insert into shows values ('Spiderman' , '2022-11-18 11:00:00', '2022-11-18 14:00:00', 1);
insert into shows values ('Batman'    , '2022-11-18 15:00:00', '2022-11-18 17:00:00', 1);
insert into shows values ('Antman'    , '2022-11-18 17:00:00', '2022-11-18 19:30:00', 1);
insert into shows values ('@reserve#2', '2022-11-18 22:00:00', '2022-11-19 00:00:00', 1);

Second, check available slots with MySQL windows functions: (@show_start and @show_end is the new schedule)

set @show_start = '2022-11-18 14:00:00';
set @show_end   = '2022-11-18 14:30:00';

with cte_avail_slots as (
select hall_id,
       row_number() over (partition by hall_id order by start_time) as avail_slot,
       end_time as slot_start,
       lead(start_time,1) over (partition by hall_id order by start_time) as slot_end
  from shows)
select hall_id,
       avail_slot,
       slot_start,
       slot_end
  from cte_avail_slots
 where hall_id = 1
   and @show_start < @show_end
   and @show_start between slot_start and slot_end
   and @show_end   between slot_start and slot_end
;

Then, raise an error if no available slots; or insert the new schedule.

How to avoid overlapping of dates in a mysql table

1 Answers1