Я застрял в этой задаче, где, если данное время datetime находится между датой начала и окончания. Например, вот таблица данных календаря:
| id | start | end | 31 | 2017-03-15 05:21:30 | 2017-03-15 06:21:30 | 32 | 2017-03-14 06:25:30 | 2017-03-14 06:25:30 | 33 | 2017-03-14 06:25:45 | 2017-03-14 06:25:45 И мне нужно вставить новые данные в таблицу со следующими начальными и конечными датами:
start: 2017-03-15 05:30:30 end: 2017-03-15 06:30:30
Но перед этим я должен проверить, существует ли дата начала и окончания в любых временных интервалах времени в моей таблице.
Результатом должно быть то, что данные даты начала и окончания не будут вставлены, потому что они падают или включаются между интервалом datetime id id 31 таблицы.
Как я могу это сделать с помощью запроса? Я попробовал такой запрос: Select * from calendar_date as c where c.start >= start AND c.end <= end чтобы определить, что данное начало и конец datetime существует, но результатов не найдено, и я думаю, что я испортил запрос , Я очень надеюсь, что кто-то может помочь мне проверить это.
Ну вот:
Select * from calendar_date as ,c where LEAST(c.end, end) - GREATEST(c.start, start) > 0
Если вы проверяете, перекрывает ли временной диапазон еще один, трюк состоит в том, чтобы проверить, есть ли какие-либо строки со следующими условиями:
@end > t1.start AND @start < t1.end
Здесь @start и @end представляют начало и конец временного окна, которое я сравниваю с таблицей.
Это психически больно каждый раз, когда я смотрю на него, потому что это противоречиво просто.
Если рассматриваемое временное окно заканчивается после того, как существующее окно запускается и запускается до того, как закончится существующий конь, тогда обязательно будет перекрытие.
Доказательство логики найдено в условиях, которые вызывают выражение выше как ложное – если сравнение сравнивается с ложным, комбинированное выражение ложно:
Если новое окно не заканчивается после того, как запущено установленное окно, оно обязательно заканчивается перед запуском установленного окна и, следовательно, не перекрывается. (Условие 1, @end > t1.start ).
Если новое окно не запускается до завершения установленного окна, оно обязательно начинается после завершения установленного окна и, следовательно, не перекрывается (условие 2, @start < t1.end ).
Как написано, этот запрос не соответствует точно окольным окнам – окно, начинающееся точно в конце другого или заканчивающееся точно в начале другого – разрешено. Если вы не хотите этого, используйте >= и <= .
Чтобы заблокировать вставку, вам нужен триггер BEFORE INSERT .
DELIMITER $$ DROP TRIGGER IF EXISTS calendar_date_bi $$ CREATE TRIGGER calendar_date_bi BEFORE INSERT ON calendar_date FOR EACH ROW BEGIN IF EXISTS(SELECT * FROM calendar_date cd WHERE NEW.end > cd.start AND NEW.start < cd.end) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'overlapping date ranges are not allowed'; END IF; END $$ DELIMITER ;