MySQL overlapping dates, none conflicting

Question

I've seen lots of threads about date ranges in MySQL but I still don't seem to be able to find an answer for what I'm looking for so any help will be greatly received.

I have a MySQL table with 3 columns, date - startTime - finishTime. The date is a MySQL 'date' type field and the start and finish times are both 'time' type fields.

Say for example I have an entry in the database as follows, lets call this session 1;

date = 2011-06-30, startTime = 09:00:00, finishTime = 11:00:00

If I come to add another session I need to make sure that it doesn't conflict with an existing session. So the following would fail because it falls in between session 1 start and finish times.

date = 2011-06-30, startTime = 10:00:00, finishTime = 12:00:00

So the record can only be inserted 'AFTER' or 'BEFORE' an existing session.

I'm using PHP/MySQL and am going on the basis that a query can be run and if there 'are' matching results then, fail, if there 'arent' matching results then insert.

Thanks in advance.

Answer 1

I'm using PHP/MySQL and am going on the basis that a query can be run and if there 'are' matching results then, fail, if there 'arent' matching results then insert.

Well, try this. Here :date: is the date of the entry you are going to add, and :start-time: and :finish-time: are its start and finish times respectively.

SELECT EXISTS (
    SELECT
        1
    FROM
        TableName
    WHERE
        `date` = :date: AND
        ( :start-time: BETWEEN startTime AND finishTime OR
          :finish-time: BETWEEN startTime AND finishTime OR
          startTime BETWEEN :start-time: AND :finish-time:
          )
) AS `Clash`