How do I compare overlapping values within a row?

Question

I seem to have a problem with this SQL query:

SELECT * FROM appts 
WHERE timeStart >='$timeStart' 
AND timeEnd <='$timeEnd' 
AND dayappt='$boatdate'

The time is formatted as military time. The logistics is that a boat rental can be reserved at 7am til 1pm or 9am til 1pm or 9am til 5pm. If there is an appt within that range, it should return appts but it has proven to be inconsistent. If I pick 9am til 1pm, it will ignore appts that started with 7am even though it overlaps 9am-1pm. If I pick 9 to 5, it will return nothing even though it should with the 7am to 1pm. How do I make a SQL statement that includes the whole range from timeStart to timeEnd including those that overlap?

Answer 1

The correct check would look like this:

SELECT * FROM appts 
WHERE timeStart <='$timeEnd' 
AND timeEnd >='$timeStart' 
AND dayappt='$boatdate'

Other good explanations have been given but I'll go ahead and update it with an alternative explanation of how I visualize this myself. Most people are looking for each possible overlap, considering two time periods, they are trying to think of each combination of start and end that can make an appointment overlap. I think about it as when do two time periods not overlap which for some reason is easier for me.

Say the time period I am checking for is today, I want to find any time period that does not overlap today. There are really only two scenarios for that, either the time period starts after today (PeriodStart > EndOfToday) or the time period ends before today (PeriodEnd < StartOfToday).

Given that we havea simple test for not overlapping:
(PeriodStart > EndOfToday) OR (PeriodEnd < StartOfToday)

A quick flip around and you have a simple test for overlap:
(PeriodStart <= EndOfToday) AND (PeriodEnd >= StartOfToday)

-Shane

Answer 2

Shahkalpesh answered the question with:

I think you need an OR.

SELECT * FROM appts 
WHERE (timeStart >='$timeStart' 
OR timeEnd <='$timeEnd')
AND dayappt='$boatdate'

I posted a comment that I consider this to be wrong, giving a pair of counter-examples:

This is plain wrong - @ShaneD is correct. For example, this will pick out a booking between 05:00 and 06:00 because the actual end time is less than any of the end times you ask about. It will also pick up rentals from 18:00 onwards, for the equivalent reason.

In a response to my comment, Shahkalpesh requested:

Could you post a separate reply with data & input parameters with expected output?

Fair enough - yes. Slightly edited, the question says:

The logic is that a boat rental can be reserved

• from 7am until 1pm, or
• from 9am until 1pm, or
• from 9am until 5pm.

If there is an appointment within that range, it should return appointments but it has proven to be inconsistent. If I pick 9am until 1pm, ...

---

Enough background. We can ignore the date of the appointments, and just consider the times. I'm assuming that there is an easy way to limit the times recorded to hh:mm format; not all DBMS actually provide that, but the extension to handle hh:mm:ss is trivial.

Appointments

Row     timeStart   timeEnd   Note
  1     07:00       13:00     First valid range
  2     09:00       13:00     Second valid range
  3     09:00       17:00     Third valid range
  4     14:00       17:00     First plausibly valid range
  5     05:00       06:00     First probably invalid range
  6     18:00       22:30     Second probably invalid range

Given a search for appointments overlapping the range 09:00 - 13:00, Shahkalpesh's (simplified) query becomes:

SELECT * FROM Appointments
    WHERE (timeStart >= '09:00' OR timeEnd <= '13:00')

This will return all six rows of data. However, only rows 1, 2, 3 overlap the time period 09:00 - 13:00. If rows 1, 2, and 3 are the only valid representative appointment values, then Shahkalpesh's query produces the correct answer. However, if the row 4 (which I think is plausibly valid) is permitted, then it should not be returned. Similarly, rows 5 and 6 - if present - should not be returned. [Actually, assuming timeStart <= timeEnd for all rows in the table (and there are no NULL values to mess things up), we can see that Shahkalpesh's query will return ANY row of data for the 09:00-13:00 query because either the start time of the row is greater 09:00 or the end time is less than 13:00 or both. This is tantamount to writing 1 = 1 or any other tautology in the WHERE clause.]

If we consider ShaneD's query (as simplified):

SELECT * FROM Appointments
    WHERE timeStart <= '13:00' AND timeEnd >= '09:00'

we see that it also selects rows 1, 2, and 3, but it rejects rows 4 (because timeStart > '13:00'), 5 (because timeEnd < '09:00') and 6 (because timeStart > '13:00'). This expression is an archetypal example of how to select rows which 'overlap', counting 'meets' and 'met by' (see "Allen's Interval Algebra", for instance) as overlapping. Changing '>=' and '<=' alters the set of intervals counted as overlapping.

Answer 3

Thanks Shane, Shahkalpesh, and Jonathan.

I actually overlooked the fact that Shane "swapped" the variables (I was still using timeStart<=$timeStart when it should be timeStart <= $timeEnd). I ran with the modified statement as Jonathan/Shane suggested and it works. As Jonathan did point out, I did obviously missed out some time ranges that I should have tested against.

Now with Jonathan's explanation, I now get a better picture of my mistake is and it's helpful.