mysql - Designing non-overlapping date-time events

Question

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)

Answer 1

It is very common when working with date-time ranges that you use a range that is inclusive at the start and exclusive at the end. For example:

(using ISO8601 formatting)

Start                  End
2013-04-29T01:00:00Z - 2013-04-29T02:00:00Z
2013-04-29T02:00:00Z - 2013-04-29T03:00:00Z

A value is in range when it is less than or equal to the start, and greater than (but not equal to) the end. In the example above, 02:00 belongs to the second range, not the first one. In other words:

Start <= value < End 

Or equivalently,

Start <= value  AND  End > value

In Mathematics, using Interval Notation, this is known as a "half-open" interval.

[Start, End)

This is always a better approach than the idea of using a value like 01:59:59. Consider if I were to subtract End - Start to get a duration. I would expect the answer be one hour, not 59 minutes and 59 seconds.

Most examples use the terms Start/End, but sometimes you will see Begin/End or Start/Stop. Personally, I think the best set of terms to use when you have an inclusive/exclusive range is Start/Until. It has the added advantage of both terms being 5 characters, lining up alphabetically, and explicitly conveying that the end date is exclusive.

Also, when you are talking about distinct events, you should record your times as UTC to prevent confusion around time zones. This is even important for local applications, as many time zones go through daylight savings transitions. You don't want the values you record in the database to be ambiguous. In MySQL, you can use the TIMESTAMP data type to make sure values are stored as UTC, or you can use the DATETIME data type if you can be sure you are working with UTC values in your application code.