Чтобы найти, когда пользователь user1 и user2 являются бесплатными, выполните следующие действия:

 select a.datetime_start as user1start,a.datetime_end as user1end, b.datetime_start as user2start,b.datetime_end as user2end , case when a.datetime_start > b.datetime_start then a.datetime_start else b.datetime_start end as avail_start, case when a.datetime_end>b.datetime_end then b.datetime_end else a.datetime_end end as avail_end from users a inner join users b on a.datetime_start<=b.datetime_end and a.datetime_end>=b.datetime_start and a.userid={user1} and b.userid={user2} 

SQL FIDDLE ЗДЕСЬ.

EDITED: для сравнения более двух пользователей, попробуйте выполнить следующие действия:

 select max(datetime_start) as avail_start,min(datetime_end) as avail_end from( select *, @rn := CASE WHEN @prev_start <=datetime_end and @prev_end >=datetime_start THEN @rn ELSE @rn+1 END AS rn, @prev_start := datetime_start, @prev_end := datetime_end from( select * from users2 m where exists ( select null from users2 o where o.datetime_start <= m.datetime_end and o.datetime_end >= m.datetime_start and o.id <> m.id ) and m.userid in (2,4,3,5) order by m.datetime_start) t, (SELECT @prev_start := -1, @rn := 1, @prev_end=-1) AS vars ) c group by rn having count(rn)=4 ; 

Необходимо изменить m.userid in (2,4,3,5) и having count(rn)=4 зависимости от количества пользователей.

SQL FIDDLE ЗДЕСЬ

Вы можете использовать это решение, чтобы найти «лучшее» временное окно, в котором могут встречаться ВСЕ пользователи по вашим критериям (допустим, userids 1-5). «Лучшее» временное окно измеряется наибольшим количеством секунд.

 SELECT MAX(b.FromDateTime) FromDateTime, a.ToDateTime FROM ( SELECT DISTINCT a.ToDateTime FROM tbl a JOIN tbl b ON a.userid <> b.userid AND a.userid IN (1,2,3,4,5) AND b.userid IN (1,2,3,4,5) AND a.ToDateTime > b.FromDateTime AND a.ToDateTime <= b.ToDateTime GROUP BY a.userid, a.FromDateTime, a.ToDateTime HAVING COUNT(DISTINCT b.userid) = 4 ) a JOIN ( SELECT DISTINCT a.FromDateTime FROM tbl a JOIN tbl b ON a.userid <> b.userid AND a.userid IN (1,2,3,4,5) AND b.userid IN (1,2,3,4,5) AND a.FromDateTime >= b.FromDateTime AND a.FromDateTime < b.ToDateTime GROUP BY a.userid, a.FromDateTime, a.ToDateTime HAVING COUNT(DISTINCT b.userid) = 4 ) b ON b.FromDateTime < a.ToDateTime GROUP BY a.ToDateTime ORDER BY TIMESTAMPDIFF(SECOND, MAX(b.FromDateTime), a.ToDateTime) DESC LIMIT 1 

4 после COUNT(DISTINCT... – это всего лишь количество пользователей в ваших критериях минус один (так как пользователю запрещается присоединяться к себе).

То, с чем нам нужно вернуть, – это время начала и окончания собрания, на котором могут присутствовать все пользователи.

Распределение запросов

Учитывая следующие данные:

 (62, 1, '2012-07-18 00:00:00', '2012-07-18 12:00:00', 1), (63, 2, '2012-07-18 00:00:00', '2012-07-18 02:00:00', 1), (64, 2, '2012-07-18 03:00:00', '2012-07-18 05:00:00', 1), (65, 2, '2012-07-18 05:30:00', '2012-07-18 06:00:00', 1), (66, 3, '2012-07-18 00:30:00', '2012-07-18 02:30:00', 1), (67, 3, '2012-07-18 03:10:00', '2012-07-18 07:30:00', 1), (68, 4, '2012-07-18 01:10:00', '2012-07-18 03:20:00', 1), (69, 4, '2012-07-18 03:50:00', '2012-07-18 06:00:00', 1), (70, 5, '2012-07-18 01:10:00', '2012-07-18 03:20:00', 1), (71, 5, '2012-07-18 04:30:00', '2012-07-18 07:10:00', 1), (72, 1, '2012-07-18 13:00:00', '2012-07-18 14:00:00', 1), (73, 2, '2012-07-18 13:30:00', '2012-07-18 14:30:00', 1), (74, 3, '2012-07-18 14:00:00', '2012-07-18 15:00:00', 1), (75, 4, '2012-07-18 14:30:00', '2012-07-18 15:30:00', 1), (76, 5, '2012-07-18 18:00:00', '2012-07-18 19:00:00', 1); 

Позиции относительного временного интервала должны выглядеть следующим образом: (для того, чтобы все это увидеть)

 uid 1 <--------------------------------------------------------------------------------------...--------> <--------------------> uid 2 <-----------------------> <-----------------------> <----> <--------------------> uid 3 <-----------------------> <-------------------------------------------> <--------------------> uid 4 <-----------------------> <-----------------------> <--------------------> uid 5 <-----------------------> <-----------------------> <--------------------> [ 1 ] [2] [ 3 ] [ 4 ] ^ We want the start and end times of this overlap 

Цифры между скобками [ ] представляют собой временное окно, в котором все свободные пользователи перекрываются. Мы хотим совпадение # 1, поскольку оно является самым длинным. Перекрытие №1 должно быть 2012-07-18 1:10:00 до 2012-07-18 2:00:00 , поэтому наш ожидаемый результат должен быть:

 FromDateTime | ToDateTime ---------------------------------------- 2012-07-18 1:10:00 | 2012-07-18 2:00:00 

Шаг 1:

Первое, что мы должны сделать, это выяснить, каковы конечные времена всех потенциальных окон встречи. Мы делаем это, выбирая те определенные интервалы, в которых их конечные времена находятся между интервалами времени свободного времени других пользователей.

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

 SELECT DISTINCT a.ToDateTime FROM tbl a JOIN tbl b ON a.userid <> b.userid AND a.userid IN (1,2,3,4,5) AND b.userid IN (1,2,3,4,5) AND a.ToDateTime > b.FromDateTime AND a.ToDateTime <= b.ToDateTime GROUP BY a.userid, a.FromDateTime, a.ToDateTime HAVING COUNT(DISTINCT b.userid) = 4 

Оказывает:

 TODATETIME ------------------- 2012-07-18 02:00:00 2012-07-18 05:00:00 2012-07-18 06:00:00 2012-07-18 03:20:00 

Демоверсия SQLFiddle

Шаг 2:

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

 SELECT b.FromDateTime, a.ToDateTime FROM ( SELECT DISTINCT a.ToDateTime FROM tbl a JOIN tbl b ON a.userid <> b.userid AND a.userid IN (1,2,3,4,5) AND b.userid IN (1,2,3,4,5) AND a.ToDateTime > b.FromDateTime AND a.ToDateTime <= b.ToDateTime GROUP BY a.userid, a.FromDateTime, a.ToDateTime HAVING COUNT(DISTINCT b.userid) = 4 ) a JOIN ( SELECT DISTINCT a.FromDateTime FROM tbl a JOIN tbl b ON a.userid <> b.userid AND a.userid IN (1,2,3,4,5) AND b.userid IN (1,2,3,4,5) AND a.FromDateTime >= b.FromDateTime AND a.FromDateTime < b.ToDateTime GROUP BY a.userid, a.FromDateTime, a.ToDateTime HAVING COUNT(DISTINCT b.userid) = 4 ) b ON b.FromDateTime < a.ToDateTime ORDER BY a.ToDateTime, b.FromDateTime --Ordered for display purposes 

Оказывает:

 TODATETIME | FROMDATETIME ------------------------------------------ 2012-07-18 02:00:00 | 2012-07-18 01:10:00 <-- Most recent FromDateTime 2012-07-18 03:20:00 | 2012-07-18 01:10:00 2012-07-18 03:20:00 | 2012-07-18 03:10:00 <-- Most recent FromDateTime 2012-07-18 05:00:00 | 2012-07-18 01:10:00 2012-07-18 05:00:00 | 2012-07-18 03:10:00 2012-07-18 05:00:00 | 2012-07-18 04:30:00 <-- Most recent FromDateTime 2012-07-18 06:00:00 | 2012-07-18 01:10:00 2012-07-18 06:00:00 | 2012-07-18 03:10:00 2012-07-18 06:00:00 | 2012-07-18 04:30:00 2012-07-18 06:00:00 | 2012-07-18 05:30:00 <-- Most recent FromDateTime 

Самые последние FromDateTimes представляют собой начало каждого потенциального окна встречи. Мы просто хотим вытащить строки, где FromDateTime является самым последним в ToDateTime . Мы делаем это на следующем шаге, используя GROUP BY в сочетании с агрегированной функцией MAX() .

Демоверсия SQLFiddle

Шаг 3:

Затем мы используем GROUP BY на ToDateTime и MAX() на FromDateTime чтобы вытащить только самые последние FromDateTimes :

 SELECT MAX(b.FromDateTime) FromDateTime, a.ToDateTime FROM ( SELECT DISTINCT a.ToDateTime FROM tbl a JOIN tbl b ON a.userid <> b.userid AND a.userid IN (1,2,3,4,5) AND b.userid IN (1,2,3,4,5) AND a.ToDateTime > b.FromDateTime AND a.ToDateTime <= b.ToDateTime GROUP BY a.userid, a.FromDateTime, a.ToDateTime HAVING COUNT(DISTINCT b.userid) = 4 ) a JOIN ( SELECT DISTINCT a.FromDateTime FROM tbl a JOIN tbl b ON a.userid <> b.userid AND a.userid IN (1,2,3,4,5) AND b.userid IN (1,2,3,4,5) AND a.FromDateTime >= b.FromDateTime AND a.FromDateTime < b.ToDateTime GROUP BY a.userid, a.FromDateTime, a.ToDateTime HAVING COUNT(DISTINCT b.userid) = 4 ) b ON b.FromDateTime < a.ToDateTime GROUP BY a.ToDateTime 

Оказывает:

 FROMDATETIME | TODATETIME ----------------------------------------- 2012-07-18 01:10:00 | 2012-07-18 02:00:00 2012-07-18 03:10:00 | 2012-07-18 03:20:00 2012-07-18 04:30:00 | 2012-07-18 05:00:00 2012-07-18 05:30:00 | 2012-07-18 06:00:00 

Это в основном наши потенциальные временные окна. Теперь просто вопрос выбора самого длинного.

Шаг 4:

Мы используем метод выбора ORDER BY / LIMIT 1 max / min, так как нам нужна только одна строка. Мы заказываем на основе секундной разницы между временем окончания и временем начала каждой встречи, затем выбираем ту, которая имеет наибольшее количество секунд (через LIMIT 1 ), давая нам наш окончательный желаемый результат:

 SELECT MAX(b.FromDateTime) FromDateTime, a.ToDateTime FROM ( SELECT DISTINCT a.ToDateTime FROM tbl a JOIN tbl b ON a.userid <> b.userid AND a.userid IN (1,2,3,4,5) AND b.userid IN (1,2,3,4,5) AND a.ToDateTime > b.FromDateTime AND a.ToDateTime <= b.ToDateTime GROUP BY a.userid, a.FromDateTime, a.ToDateTime HAVING COUNT(DISTINCT b.userid) = 4 ) a JOIN ( SELECT DISTINCT a.FromDateTime FROM tbl a JOIN tbl b ON a.userid <> b.userid AND a.userid IN (1,2,3,4,5) AND b.userid IN (1,2,3,4,5) AND a.FromDateTime >= b.FromDateTime AND a.FromDateTime < b.ToDateTime GROUP BY a.userid, a.FromDateTime, a.ToDateTime HAVING COUNT(DISTINCT b.userid) = 4 ) b ON b.FromDateTime < a.ToDateTime GROUP BY a.ToDateTime ORDER BY TIMESTAMPDIFF(SECOND, MAX(b.FromDateTime), a.ToDateTime) DESC LIMIT 1 

SQLFiddle Демо итогового результата

Демоверсия SQLFiddle с другими примерами данных

Получение времени встречи между всеми пользователями в таблице (без критериев):

Если вы не хотите указывать, для каких пользователей вы хотите проверять время встречи (просто сделайте это для всех пользователей в таблице), вы можете использовать:

 SELECT MAX(b.FromDateTime) FromDateTime, a.ToDateTime FROM ( SELECT DISTINCT a.ToDateTime FROM tbl a JOIN tbl b ON a.userid <> b.userid AND a.ToDateTime > b.FromDateTime AND a.ToDateTime <= b.ToDateTime CROSS JOIN (SELECT COUNT(DISTINCT userid) totalusers FROM tbl) c GROUP BY a.userid, a.FromDateTime, a.ToDateTime, c.totalusers HAVING COUNT(DISTINCT b.userid) = c.totalusers-1 ) a JOIN ( SELECT DISTINCT a.FromDateTime FROM tbl a JOIN tbl b ON a.userid <> b.userid AND a.FromDateTime >= b.FromDateTime AND a.FromDateTime < b.ToDateTime CROSS JOIN (SELECT COUNT(DISTINCT userid) totalusers FROM tbl) c GROUP BY a.userid, a.FromDateTime, a.ToDateTime, c.totalusers HAVING COUNT(DISTINCT b.userid) = c.totalusers-1 ) b ON b.FromDateTime < a.ToDateTime GROUP BY a.ToDateTime ORDER BY TIMESTAMPDIFF(SECOND, MAX(b.FromDateTime), a.ToDateTime) DESC LIMIT 1 

Я создал 1D алгоритм пересечения сегментов линии в PHP, используя линию развертки ( Википедия ). Он работает, потому что datetimes могут быть сопоставлены с числовой строкой: например, с использованием «миллисекунд с эпохи».

См. Реализацию здесь: http://pastebin.com/iLwJQEF0

Алгоритм выводит массив пересечений линейных сегментов (которые также являются линейными сегментами), которые также имеют список всех пользователей, доступных на время. Вы можете сортировать перекрестки по определению «лучший» (и отменить его для спуска): сначала количество доступных пользователей, а затем их продолжительность. (Уже реализовано!)

Он работает в O(n * log n) , где n – количество периодов времени.

Заметки:

• Если вы не хотите вмешиваться в конверсии datetime-to-millisecond, вы можете заменить вычитание и больше или меньше, чем операторов. (Я оставил для вас некоторые комментарии).
• Важно следить за линейными сегментами, которые начинаются / заканчиваются в одном и том же месте:
  • Строка развертки должна сталкиваться с конечными точками перед пусковыми точками того же значения.
  • Также обратите внимание, что он не будет создавать посторонние результаты, когда два линейных сегмента заканчиваются с одним и тем же значением.
• Я уверен, что это может быть повторно реализовано внутри механизма базы данных (если вы считаете, что это того стоит). Ряд поставщиков баз данных имеют геометрические расширения.

Я нашел хакерский способ сделать это:

Perl имеет некоторую вещь, называемую Set::IntSpan которая имеет функцию intersect (или метод), которая найдет диапазон, общий для двух интервалов чисел. Идея состоит в том, чтобы использовать его.

Вы можете преобразовать даты времени строки в timestamp (numbers) с помощью strtotime("2012-08-27 02:02:02") в php. Когда у вас есть две пары временных меток, вы можете использовать следующий примерный код perl, чтобы найти интервал пересечения, из которого вы можете найти время.

 use Set::IntSpan; my $r1 = Set::IntSpan->new([ 5 .. 15 ]); my $r2 = Set::IntSpan->new([ 2 .. 20 ]); my $i = $r1->intersect($r2); if ( !$i->empty and ( $i->max - $i->min ) >= 5 ) # criteria { print "hit\n"; # $i->max, $i->min are the timestamps you need } else { print "miss\n"; } 

как только у вас есть интервал пересечения, вы можете вернуть дату с отметки времени (если вам нужно) с использованием date("Ymd H:i:s", $timestamp);

Вот некоторые ссылки и ссылки:

Рассчитать перекрытие между двумя диапазонами чисел

Вызов Perl-скрипта из PHP и передача в переменных, а также использование имени переменной perl-скрипта

ps, возможно, perl pros может завернуть код в функцию с 4 аргументами? Кроме того, я понимаю, что это не идеальный ответ на вопрос, но идея идея крутая.

Используя схему Sel от скрипки (10x sel) …

Самый простой способ сделать это:

 SELECT MAX(GREATEST(u1.datetime_start, u2.datetime_start)) AS MeetingStart, MIN(LEAST(u1.datetime_end, u2.datetime_end)) AS MeetingEnd FROM users2 u1 INNER JOIN users2 u2 ON (u1.datetime_end >= u2.datetime_start AND u1.datetime_start <= u2.datetime_end) AND u2.userid != u1.userid AND u2.userid IN (3,4,5) WHERE u1.userid=2 GROUP BY u1.id HAVING COUNT(DISTINCT u2.userid) = 3 AND MeetingStart < MeetingEnd 

Измените в зависимости от вашей ситуации:

В моем примере у нас 4 участника. n = 4, участники (2,3,4,5)

IN (3,4,5) -> последний n-1 id участников на встречу

WHERE u1.userid = 2 -> id для первого участника собрания

ИМЕЕТ СЧЕТ (DISTINCT u2.userid) = 3 -> n – 1

Может быть протестирован на sqlfiddle