检查表是否有时间重叠?

I have a MySQL table with the following fields:

我有一个MySQL表，包含以下字段:

name
的名字
starttime
开始时间
endtime
endtime

starttime and endtime are MySQL TIME fields (not DATETIME). I need a way to periodically "scan" the table to see if there are any overlaps in time ranges within the table. If there is an event from 10:00-11:00 and another from 10:30-11:30, I want to be alerted of the presence of the time overlap.

starttime和endtime是MySQL的时间字段(不是DATETIME)。我需要一种方法来周期性地“扫描”表，以查看表中是否存在时间范围的重叠。如果在10:00-11:00和10:30-11:30之间有一个事件发生，我想提醒大家注意时间重叠的存在。

Nothing fancy really, all I want to know whether an overlap exists or not.

没什么特别的，我只想知道重叠是否存在。

I'm going to be using PHP to execute this.

我要用PHP来执行。

4 个解决方案

#1

This is a query pattern for which I found the answer many years ago:

这是一个查询模式，我多年前就找到了答案:

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

To find "any overlap", you compare the opposite ends of the timeframe with each other. It's something I had to get a pen and paper out for and draw adjacent ranges to realise that the edge cases boiled down to this comparison.

要找到“任何重叠”，你需要将时间框架的两端相互比较。我必须拿出笔和纸，画出相邻的范围，才能意识到，这些边界可以归结为这个比较。

If you want to prevent any rows from overlapping, put a variant of this query in a trigger:

如果您想防止任何行重叠，请将此查询的一个变体放在触发器中:

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

I wanted a generic function to check if two time ranges for days overlap which would also work with cases where the schedule starts before midnight and ends after, like "17:00:00-03:00:00" and "14:00:00-01:00:00" should overlap, so I modified the solution by Bohemian

我想要一个通用的函数来检查两个时间段是否有重叠，这也适用于在午夜之前开始的情况，然后在结束之后，比如“17:00:00-03:00”和“14:00:00-01:00”应该重叠，所以我修改了波希米的解决方案。

you use this function as follows

使用如下函数。

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

or in your case like this

或者像这样

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

Here is the function definition

这是函数的定义

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

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.

我没有使用TIME(CAST(a_start作为DATETIME));为了将时间间隔转换为一个时间，因为它默认使用当前的日期，并且可能会受到数据库时区设置的影响，请想象第二天发生DST变化。

If your database is using UTC timezone (as it should) then you can use this

如果您的数据库正在使用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

Try this:

试试这个:

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  

    )

#4

Try this, it works for me

试试这个，对我有用

SELECT * from Shedulles a 
where exists 
( select 1 from Shedulles b 
    where 
    a.ShedulleId != b.ShedulleId 
    and ( a.DateFrom between b.DateFrom and b.DateTo 
    or a.DateTo between b.DateFrom and b.DateTo 
    or b.DateFrom between a.DateFrom and a.DateTo ) 
    and a.DateFrom != b.DateTo 
    and b.DateFrom != a.DateTo 
);

Or this one

或者这一次

SELECT DISTINCT a.* FROM Shedulles a
JOIN Shedulles b 
    ON 
    a.ShedulleId != b.ShedulleId 
    and ( a.DateFrom between b.DateFrom and b.DateTo 
    or a.DateTo between b.DateFrom and b.DateTo 
    or b.DateFrom between a.DateFrom and a.DateTo ) 
    and a.DateFrom != b.DateTo 
    and b.DateFrom != a.DateTo

#1