Перекрывающиеся даты MySQL, не конфликтующие

Я видел много тем о диапазонах дат в MySQL, но я все еще не могу найти ответ на то, что я ищу, поэтому любая помощь будет принята с благодарностью.

У меня есть таблица MySQL с 3 столбцами, дата - время начала - время окончания. Дата является полем типа «дата» MySQL, а время начала и окончания являются полями типа «время».

Скажем, например, у меня есть запись в базе данных следующим образом, давайте назовем эту сессию 1;

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

Если я приду, чтобы добавить еще один сеанс, мне нужно убедиться, что он не конфликтует с существующим сеансом. Таким образом, следующее не удастся, потому что оно попадает между временем начала и окончания сеанса 1.

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

Таким образом, запись может быть вставлена ​​только «ПОСЛЕ» или «ДО» существующей сессии.

Я использую PHP/MySQL и исхожу из того, что запрос может быть запущен, и если есть совпадающие результаты, то произойдет сбой, если нет совпадающих результатов, затем вставьте.

Заранее спасибо.


mysql php date range overlapping
person Rob    schedule 29.06.2011    source источник
comment
И что происходит, когда есть сессия с 09:00 - 11:00, а новый — 11:00 - 13:00. Столкновение или нет?   -  person ypercubeᵀᴹ    schedule 29.06.2011
comment
@Rob: Тогда решения BETWEEN не будут работать, так как они приведут к столкновению.   -  person ypercubeᵀᴹ    schedule 01.07.2011

Ответы (5)


arrow_upward
4
arrow_downward

Я использую PHP/MySQL и исхожу из того, что запрос может быть запущен, и если есть совпадающие результаты, то ошибка, если нет совпадающих результатов, тогда вставка.

Что ж, попробуй это. Здесь :date: — это дата записи, которую вы собираетесь добавить, а :start-time: и :finish-time: — время ее начала и окончания соответственно.

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`
person Hammerite    schedule 29.06.2011
comment
Работает отлично! Спасибо большое! - person Rob; 29.06.2011
comment
Я только что понял, что это сообщает о столкновении, если два сеанса сталкиваются только в своих конечных точках (т.е. конечная точка одного является началом другого). Это может быть не то, что вы намереваетесь. Это происходит потому, что BETWEEN является инклюзивным. Запрос можно переписать, чтобы исключить использование BETWEEN в пользу операторов строгого сравнения < и >. - person Hammerite; 29.06.2011
comment
@Hammerite: Точно. x BETWEEN a AND b, его следует заменить на a <= x AND x < b - person ypercubeᵀᴹ; 01.07.2011

arrow_upward
1
arrow_downward

Я столкнулся с этой проблемой обнаружения перекрытия дат, моя первая идея состояла в том, чтобы сделать что-то вроде решения Hammerite, но я нашел это решение неполным, главным образом потому, что существует слишком много возможных сценариев, когда два диапазона дат могут конфликтовать:

Запрос, который я использовал для решения этой проблемы, был примерно таким:

/* overlapping dates */
SELECT * 
FROM my_table
WHERE `date` = $date AND
  NOT (
    `start_time` < $start_time and `finish_time` < $start_time
    OR
    `start_time` > $end_time and `finish_time` > $end_time
  )

Для получения более подробной информации вы можете посетить мой блог здесь

person emont01    schedule 21.02.2013

arrow_upward
0
arrow_downward

Я бы по-другому структурировал таблицу. У меня было бы два столбца, оба типа datetime, с именами session_start и session_end.

Логика такова: вы не можете вставить новую сессию, если ее session_start время не > или <, чем старая сессия session_end.

person Michael J.V.    schedule 29.06.2011

arrow_upward
0
arrow_downward

Предполагая, что $date, $startTime и $finishTime являются вашими переменными PHP, которые хранят дату, время начала и время окончания соответственно для вставки.

$query = 'INSERT INTO `session`
SELECT \'' . $date . '\', \'' . $startTime . '\', \'' . $finishTime . '\'
FROM `session`
WHERE NOT EXISTS (SELECT *
    FROM `session`
    WHERE `date` = \'' . $date . '\'
    AND \'' . $startTime . '\' BETWEEN `startTime` and `finishTime`
)';

Надеюсь это поможет.

person Abhay    schedule 29.06.2011
comment
Что делать, если время начала нового сеанса находится не между временем начала и окончания существующего сеанса, а временем его окончания? - person Hammerite; 29.06.2011
comment
Да, мой плохой, я пропустил, чтобы добавить эти условия. Спасибо, что указали на них - person Abhay; 29.06.2011

arrow_upward
0
arrow_downward

Я бы использовал простой:

INSERT INTO session
  ( `date`, startTime, finishTime )
  SELECT
  ( $date, $startTime, $finishTime )
  WHERE NOT EXISTS
    ( SELECT
          *
      FROM
          session
      WHERE
            `date` = $date 
        AND $startTime <  finishTime 
        AND  startTime < $finishTime 

    )

< следует изменить на <=, если вы хотите, чтобы два периода 09:00 - 11:00 и 11:00 - 13:00 совпадали.

person ypercubeᵀᴹ    schedule 29.06.2011