I have the following problem:
Events have a "begin" and "end" time and an amount. I use MySQL DATETIME for both.
Now if I have a constraint that says "no overlapping events" I need to make some checks etc. but how to design that? The user only needs 5-mins precision or so, but i want to do calculations with seconds since that is "simpler"/"cleaner"
If I have an event (A) with start-end "YYYY-MM-DD 12:00:00"-"YYYY-MM-DD 14:00:00"
and another
(B) "YYYY-MM-DD 14:00:00"-"YYYY-MM-DD 16:15:00" -> they are non-overlapping even though they both contain 14:00:00.
In order to determine that they are indeed non-overlapping should I use
A.end < B.begin etc.
Or
A.end <= B.begin etc. AND make a "hack" such that all end-times are "DATE HH:MM:00 (minus one second)" instead of "DATE HH:MM:00" i.e. A.end would then be "YYYY-MM-DD 13:59:59" instead of "YYYY-MM-DD 14:00:00"
The first is simplest, but will it miss any overlaps if I have a lot of different events and need to check that i don't "overbook" i.e. that the events individual amounts don't exceed the total amount (example: if each event is a booking of a table with a number of persons, I can't exceed the total number of tables at any given time)
See Question&Answers more detail:os