У меня есть таблица в mysql:
+------------+------------+------------+------------+ | date | user_id | start_hour | end_hour | +------------+------------+------------+------------+ | 2010-12-15 | 20 | 08:00:00 | 08:15:00 | | 2010-12-15 | 20 | 14:00:00 | 14:30:00 | | 2010-12-15 | 20 | 17:00:00 | 17:45:00 | +------------+------------+------------+------------+ и я пытаюсь извлечь временной диапазон времени пользователей, которых я нашел, и пример здесь , но я не могу сделать эту работу часами
Я попробовал запрос:
$sql=" SELECT a.end_hour AS 'Available From', Min(b.start_hour) AS 'To' FROM ( SELECT 0 as date, '08:00:00' as start_hour,'08:00:00' as end_hour UNION SELECT date, start_hour, end_hour FROM table ) AS a JOIN ( SELECT date, start_hour, end_hour FROM table UNION SELECT 0, '21:00:00' as start_hour, '22:00:00' as end_hour ) AS b ON a.date=b.date AND a.user_id=b.user_id AND a.end_hour < b.start_hour WHERE a.date='$date' AND a.user_id='$user_id' GROUP BY a.end_hour HAVING a.end_hour < Min(b.start_hour);";
Мне нужно создать диапазон с 08:00 до 21:00 со свободными блоками между встречами вроде этого:
free time 08:15:00 to 14:00:00 14:30:00 to 17:00:00 17:45:00 to 21:00:00
Попробуйте этот запрос
SELECT a.id, a.start_hour, a.end_hour, TIMEDIFF(la.start_hour, a.end_hour) as `Free Time` FROM appointment as a LEFT JOIN(SELECT * FROM appointment LIMIT 1,18446744073709551615) AS la ON la.id = a.id + 1 LEFT JOIN (SELECT * FROM appointment) AS ra ON a.id = ra.id
Это покажет эти результаты
+---------------------------------------------+ ¦ id ¦ start_hour BY ¦ end_hour | Free Time | ¦----+---------------¦------------------------| ¦ 1 ¦ 08:00:00 ¦ 08:15:00 | 05:45:00 | ¦ 2 ¦ 14:00:00 ¦ 14:30:00 | 02:30:00 | ¦ 3 ¦ 17:00:00 ¦ 17:45:00 | 03:15:00 | ¦ 4 ¦ 21:00:00 ¦ 21:00:00 | (NULL) | +--------------------+------------------------+
Также у вас должно быть 21:00:00 в таблице, иначе вы не сможете получить последнюю разницу во времени. я вошел в 21:00:00 в качестве начальной и конечной даты в таблице.
Это измененный запрос
SELECT a.id, a.end_hour AS `Free time Start`, IFNULL(la.start_hour,a.end_hour) AS `Free Time End`, IFNULL(TIMEDIFF(la.start_hour, a.end_hour),'00:00:00') AS `Total Free Time` FROM appointment AS a LEFT JOIN (SELECT * FROM appointment LIMIT 1,18446744073709551615) AS la ON la.id = (SELECT MIN(id) FROM appointment where id > a.id LIMIT 1)
И результат
+--------------------------------------------------------+ ¦ id ¦ Free time Start ¦ Free Time End | Total Free Time | ¦----+-----------------¦---------------------------------| ¦ 1 ¦ 08:15:00 ¦ 14:00:00 | 05:45:00 | ¦ 2 ¦ 14:30:00 ¦ 17:00:00 | 02:30:00 | ¦ 3 ¦ 17:45:00 ¦ 21:00:00 | 03:15:00 | ¦ 4 ¦ 21:00:00 ¦ 21:00:00 | 00:00:00 | +----------------------+---------------------------------+
Вопросы, которые следует изучить из этого запроса,
Попробуйте этот запрос, я надеюсь, что его работа для вас …..
SELECT a.id, a.end_hour AS `Free time Start`, la.start_hour AS `Free Time End`, IFNULL(TIMEDIFF(la.start_hour, a.end_hour),'00:00:00') AS `Total Free Time` FROM appointment AS a LEFT JOIN (SELECT * FROM appointment LIMIT 1,18446744073709551615) AS la ON la.id = (SELECT id FROM appointment WHERE id NOT IN(a.id) ORDER BY (a.id > id)ASC LIMIT 1);
Это даст вам блоки времени окончания
SELECT T_a.end_hour FROM (table AS T_a) LEFT JOIN (table AS T_b) ON (T_a.`date`=T_b.`date` AND T_b.start_hour<=T_a.end_hour AND T_b.end_hour>T_a.end_hour) WHERE T_b.user_id IS NULL
Это даст вам блок времени начала
SELECT T_a.start_hour FROM (table AS T_a) LEFT JOIN (table AS T_b) ON (T_a.`date`=T_b.`date` AND T_b.start_hour<T_a.start_hour AND T_b.end_hour>=T_a.start_hour) WHERE T_b.user_id IS NULL
Эти запросы будут искать начальные или конечные значения, которые не пересекаются или забиваются до других слотов. поэтому 10-11 11-12 даст 10 старта, 12 end = свободное время – 08-10 и 12-21
Таким образом, свободное время составляет 08:00 (если только его время начала) до начала «начала», а затем пары пар END-> START до последнего END-> 21:00 (если только 21:00 не является последним)
Примечание. Также нужно добавить дату.
Возможно, это больше, чем убить, но это работает на sql-сервере, не уверен, что попытка конвертировать в mysql
declare @AvailableTime table ( AvailableDate date, StartTime time, EndTime time ) insert into @AvailableTime values ('15 dec 2010', '08:00:00', '21:00:00') declare @Booking table (BookingDate date, UserId int, StartTime Time, EndTime Time) insert into @Booking values ('15 dec 2010', 20, '08:00:00', '08:15:00'), ('15 dec 2010', 20, '14:00:00', '14:30:00'), ('15 dec 2010', 20, '17:00:00', '17:45:00') select * from ( -- first SELECT get start boundry select t.StartTime s, b.StartTime e from @AvailableTime t, @Booking b where t.AvailableDate = b.BookingDate and (t.StartTime <= b.EndTime and b.StartTime <= t.EndTime) and t.StartTime <> b.StartTime and not exists (select 1 from @Booking b2 where b2.BookingDate = b.BookingDate and b2.StartTime < b.StartTime) union -- second SELECT get spikes ie middle select b1.EndTime s, b2.StartTime e from @AvailableTime t, @Booking b1, @Booking b2 where t.AvailableDate = b1.BookingDate and (t.StartTime <= b1.EndTime and b1.StartTime <= t.EndTime) and t.AvailableDate = b2.BookingDate and (t.StartTime <= b2.EndTime and b2.StartTime <= t.EndTime) and b1.EndTime < b2.StartTime and not exists (select 1 from @Booking b3 where b3.BookingDate = t.AvailableDate and b3.StartTime > b1.EndTime and b3.EndTime < b2.StartTime) union -- third SELECT get end boundry select b.EndTime s, t.EndTime e from @AvailableTime t, @Booking b where t.AvailableDate = b.BookingDate and (t.StartTime <= b.EndTime and b.StartTime <= t.EndTime) and t.EndTime <> b.EndTime and not exists (select 1 from @Booking b2 where b2.BookingDate = b.BookingDate and b2.StartTime > b.StartTime) ) t1