MySQL重叠日期，没有冲突 [英] MySQL overlapping dates, none conflicting

问题描述

我在MySQL中看过很多关于日期范围的线程，但是我仍然似乎无法找到我正在寻找的答案，所以任何帮助将被大大的收获。

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.

我有一个具有3列，date - startTime - finishTime的MySQL表。日期是一个MySQL'date'类型字段，开始和结束时间都是'时间'类型的字段。

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.

例如说我在数据库中有一个条目接下来，让我们调用这个会话1;

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

如果我来添加另一个会话，我需要确保它不'与现有会话冲突。所以以下将会失败，因为它在会话1开始和结束时间之间。

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

所以记录只能插入AFTER或BEFORE现有会话之前。

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

我正在使用PHP / MySQL，并且可以运行查询，如果有匹配结果然后，失败，如果没有匹配的结果然后插入。

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.

提前感谢。

推荐答案

我正在使用PHP / MySQL，并且基于一个查询可以运行，如果有'匹配的结果，那么失败，如果没有'匹配结果然后插入。

嗯，试试看。这里：date：是您要添加的条目的日期，：start-time：和：完成时间：分别是其开始和结束时间。

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`

这篇关于MySQL重叠日期，没有冲突的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！