Проверить перекрытие диапазонов дат в MySQL

У меня был такой запрос с приложением-календарем, которое я когда-то написал. Думаю, я использовал что-то вроде этого:

... WHERE new_start < existing_end
      AND new_end   > existing_start;

ОБНОВЛЕНИЕ. Это обязательно должно сработать ((ns, ne, es, ee) = (new_start, new_end, existing_start, existing_end)):

1. ns - ne - es - ee: не перекрывается и не совпадает (потому что необходимо)
2. ns - es - ne - ee: совпадают и совпадают
3. es - ns - ee - ne: перекрывается и совпадает
4. es - ee - ns - ne: не перекрывается и не совпадает (потому что ns> ee)
5. es - ns - ne - ee: перекрывается и совпадает
6. ns - es - ee - ne: перекрывается и совпадает

Вот скрипка

SELECT * FROM tbl WHERE
existing_start BETWEEN $newStart AND $newEnd OR 
existing_end BETWEEN $newStart AND $newEnd OR
$newStart BETWEEN existing_start AND existing_end

if (!empty($result))
throw new Exception('We have overlapping')

Эти 3 строки предложений sql охватывают 4 случая необходимого перекрытия.

Ответ Лами хороший, но вы можете еще немного его оптимизировать.

SELECT * FROM tbl WHERE
existing_start BETWEEN $newSTart AND $newEnd OR
$newStart BETWEEN existing_start AND existing_end

Это позволит уловить все четыре сценария, в которых диапазоны перекрываются, и исключить два, где они не совпадают.

Я столкнулся с подобной проблемой. Моя проблема заключалась в том, чтобы остановить бронирование между несколькими заблокированными датами. Например, со 2 по 7 мая бронирование для объекта размещения заблокировано. Мне нужно было найти любую совпадающую дату, чтобы обнаружить и остановить бронирование. Мое решение похоже на LordJavac.

SELECT * FROM ib_master_blocked_dates WHERE venue_id=$venue_id AND 
(
    (mbd_from_date BETWEEN '$from_date' AND '$to_date') 
    OR
    (mbd_to_date BETWEEN  '$from_date' AND '$to_date')
    OR
    ('$from_date' BETWEEN mbd_from_date AND mbd_to_date)
    OR      
    ('$to_date' BETWEEN mbd_from_date AND mbd_to_date)      
)
*mbd=master_blocked_dates

Сообщите мне, если это не сработает.

Учитывая два интервала, например (s1, e1) и (s2, e2) с s1 ‹e1 и s2‹ e2,
Вы можете вычислить перекрытие следующим образом:

SELECT 
     s1, e1, s2, e2,
     ABS(e1-s1) as len1,
     ABS(e2-s2) as len2,
     GREATEST(LEAST(e1, e2) - GREATEST(s1, s2), 0)>0 as overlaps,
     GREATEST(LEAST(e1, e2) - GREATEST(s1, s2), 0) as overlap_length
FROM test_intervals 

Также будет работать, если один интервал находится внутри другого.

Недавно я боролся с той же проблемой и закончил одним простым шагом (это может быть не лучшим подходом или потреблением памяти) -

SELECT * FROM duty_register WHERE employee = '2' AND (
(
duty_start_date BETWEEN {$start_date} AND {$end_date}
OR
duty_end_date BETWEEN {$start_date} AND {$end_date}
)
OR
(
{$start_date} BETWEEN duty_start_date AND duty_end_date
OR
{$end_date} BETWEEN duty_start_date AND duty_end_date)
);

Это помогло мне найти записи с перекрывающимися диапазонами дат.

Надеюсь, это кому-то поможет.

Вы можете охватить все случаи перекрытия дат, даже если текущая дата в базе данных может быть нулевой, как показано ниже:

SELECT * FROM `tableName` t
WHERE t.`startDate` <= $toDate
AND (t.`endDate` IS NULL OR t.`endDate` >= $startDate);

Это вернет все записи, которые так или иначе совпадают с новыми датами начала / окончания.

Приведенный выше ответ Mackraken лучше с точки зрения производительности, поскольку он не требует нескольких OR, чтобы оценить, перекрываются ли две даты. Хорошее решение!

Однако я обнаружил, что в MySQL вам нужно использовать DATEDIFF вместо оператора минус -

SELECT o.orderStart, o.orderEnd, s.startDate, s.endDate
, GREATEST(LEAST(orderEnd, endDate) - GREATEST(orderStart, startDate), 0)>0 as overlaps
, DATEDIFF(LEAST(orderEnd, endDate), GREATEST(orderStart, startDate)) as overlap_length
FROM orders o
JOIN dates s USING (customerId)
WHERE 1
AND DATEDIFF(LEAST(orderEnd, endDate),GREATEST(orderStart, startDate)) > 0;