Back when I was in college, one of my employers had me work on a scheduling application. Basically, I helped make a PHP-based application which allowed managers to schedule their students at anytime of day or week.

At one point we had devised that an entire working block would be one entry in the database with a timestamp representing the start and end times of a block. We needed a way to pull all data for a given day.

The following data was available for this:

• event_start - each working block had a starting timestamp associated with it.
• event_end – coupled with event_start, this defined the end of the working block (also a timestamp).
• range_start – the beginning of the range we wanted to find all events for; in this example, it'll be midnight of some day.
• range_end – the end of the range being worked with, happens to be 11:59:59 on the same day as range_start in this example.

The events were all stored in our MySQL database, with the range_start and range_end values being calculated as needed. Our first crack at this problem failed to cover all our needs:
SELECT ... WHERE event_start >= range_start AND event_end <= range_end
This query only selects events which start and end in a given day. Sadly, events could carry-over from other days (or even span multiple days), which this query obviously could not handle.

After thinking long and hard, I came up with the following:
SELECT ... WHERE event start <= range_end AND event_end > range_start
This query really sent my fellow programmers for a loop because it looked so similar to the previous one. All events from the first query were still returned with this new query, as were all events that end in this range, but do not start in it; all events that start in this range, but don't end it in; and all events that start before this range and end after it.

Two changes to the query allowed it to pull back all this extra information: 1) I swapped the range_start and range_end variables, and 2) I reversed the >= and <= operators, and dropped the second =. To understand why this works, it is best to separate the query on the AND.

In the first query, the event_start >= range_start by itself finds all events that start from range_start through infinity. We certainly want all events that start after range_start, but we don't really care about what events start after range_end. So, in actuality we want all events that start before range_end, which gives us the new event_start <= range_end. This same reasoning is used to explain the change from event_end <= range_end to event_end > range_start: we only care about events that end after range_start.