Проверка таблицы для перекрытия времени?

Ответ 1

Это шаблон запроса, для которого я нашел ответ много лет назад:

SELECT *
FROM mytable a
JOIN mytable b on a.starttime <= b.endtime
    and a.endtime >= b.starttime
    and a.name != b.name; -- ideally, this would compare a "key" column, eg id

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

Если вы хотите предотвратить перекрытие любых строк, поместите вариант этого запроса в триггер:

create trigger mytable_no_overlap
before insert on mytable
for each row
begin
  if exists (select * from mytable
             where starttime <= new.endtime
             and endtime >= new.starttime) then
    signal sqlstate '45000' SET MESSAGE_TEXT = 'Overlaps with existing data';
  end if;
end;

Ответ 2

Мне нужна общая функция, чтобы проверить, будут ли два временных интервала для перекрытия дней, которые также будут работать с случаями, когда расписание начинается до полуночи и заканчивается после, например, "17: 00: 00-03: 00: 00" и "14: 00: 00-01: 00: 00" должно перекрываться, поэтому я изменил решение по-чешски.

вы используете эту функцию следующим образом

SELECT func_time_overlap("17:00:00","03:00:00", "14:00:00","01:00:00")

или в вашем случае, как это

SELECT *
FROM mytable a
JOIN mytable b ON (
    a.name != b.name 
    AND func_time_overlap(a.starttime, a.endtime, b.starttime, b.endtime)
);

Вот определение функции

CREATE FUNCTION `func_time_overlap`(a_start TIME, a_end TIME, b_start TIME, b_end TIME) 
RETURNS tinyint(1) 
DETERMINISTIC
BEGIN

-- there are only two cases when they don't overlap, but a lot of possible cases where they do overlap

-- There are two time formats, one is an interval of time that can go over 24 hours, the other is a daily time format that never goes above 24 hours
-- by default mysql uses TIME as an interval
-- this converts a TIME interval into a date time format

-- I'm not using `TIME(CAST(a_start AS DATETIME));` to convert the time interval to a time
-- because it uses the current day by default and might get affected by the timezone settings of the database, 
-- just imagine the next day having the DST change.
-- although the CAST should work fine if you use UTC

IF a_start >= 24 THEN 
    SET a_start = TIME(CONCAT(MOD(HOUR(a_start), 24),':',MINUTE(a_start),':',SECOND(a_start))); 
END IF;

IF b_start >= 24 THEN 
    SET b_start = TIME(CONCAT(MOD(HOUR(b_start), 24),':',MINUTE(b_start),':',SECOND(b_start))); 
END IF;

IF a_end > 24 THEN 
    SET a_end = TIME(CONCAT(MOD(HOUR(a_end), 24),':',MINUTE(a_end),':',SECOND(a_end))); 
END IF;

IF b_end > 24 THEN 
    SET b_end = TIME(CONCAT(MOD(HOUR(b_end), 24),':',MINUTE(b_end),':',SECOND(b_end))); 
END IF;


-- if the time range passes the midnight mark, then add 24 hours to the time
IF a_start >= a_end THEN 
    SET a_end = a_end + INTERVAL 24 HOUR; 
END IF;

IF b_start >= b_end THEN 
    SET b_end = b_end + INTERVAL 24 HOUR; 
END IF;

RETURN a_start < b_end AND a_end > b_start;


END

Я не использую TIME(CAST(a_start AS DATETIME));, чтобы преобразовать временной интервал в какое-то время, потому что он использует текущий день по умолчанию и может пострадать от настроек часового пояса базы данных, просто представьте, что на следующий день произойдет изменение DST.

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

IF a_start >= 24 THEN 
    SET a_start = TIME(CAST(a_start AS DATETIME)); 
END IF;

IF b_start >= 24 THEN 
    SET b_start = TIME(CAST(b_start AS DATETIME)); 
END IF;

IF a_end > 24 THEN 
    SET a_end = TIME(CAST(a_end AS DATETIME));
END IF;

IF b_end > 24 THEN 
    SET b_end = TIME(CAST(b_end AS DATETIME));
END IF;

Ответ 3

Попробуйте следующее:

declare @tempTbl table(RecID)

    insert into @tempTbl
    Select RecID
    from 
    (
    Select t.RecID from Table1 t,Table1 t1
    where t.StartTime between t1.StartTime AND t1.EndTime
    AND t.RecID <> t1.RecID  

    )