检查表格时间重叠？

Question

我有一个MySQL表格，拥有以下字段:

• name (名称)
• starttime (开始时间)
• endtime (结束时间)

starttime 和 endtime 是MySQL的TIME字段（不是DATETIME）。 我需要一种方式定期“扫描”该表以查看表内时间范围之间是否存在重叠。如果有一个事件从10:00-11:00，另一个事件从10:30-11:30，那么我想要被提醒这些时间段存在重叠。

并没有什么花哨的东西，我只想知道是否存在重叠。

我将使用PHP来执行此操作。

- Danish M.

Answer 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;

- Bohemian

---

对于这种奇怪的情况也无法工作（但应该可以）："17:00:00-03:00:00"，"14:00:00-01:00:00"，问题是时间循环，并且很难控制它给出的数字，特别是在转换时区时。 - Timo Huovinen

@Bohemian 谢谢您的想法，但我想把它变成一个通用的mysql函数，接受4个时间参数，问题是TIME没有时代秒数，因为它不是DATETIME。 - Timo Huovinen

@timo 无论你有什么，最终都可以将其转换为某个绝对时间值，例如 小时 * 60 + 分钟。 - Bohemian

@Bohemian mysql已经像处理整数一样正确地处理TIME类型比较，我不明白它如何解决传递为00:00的时间实际上表示24:00或查看另一个情况"17:00:00-03:00:00"， "14:00:00-01:00:00"成为SELECT 61200 <= 3600 and 10800 >= 50400，这是错误的，尽管它们重叠。 - Timo Huovinen

@Bohemian 好的，搞定了！:D 解决方案是使用 MOD 或 CAST 将时间间隔转换为日期时间格式，对于结束时间早于开始时间的情况，只需添加 24 小时（因为实际上就是这样），我将其发布为答案，如果您感兴趣，请查看，谢谢您的帮助。 - Timo Huovinen

Answer 2

我需要一个通用函数来检查两个时间范围是否在同一天内重叠，该函数还适用于计划从午夜之前开始到午夜之后结束的情况，例如"17:00:00-03:00:00"和"14: 00:00-01:00:00"应该重叠，因此我修改了Bohemian的解决方案（链接）

您可以按以下方式使用此函数

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));将时间间隔转换为时间，因为它默认使用当前日期，并可能受到数据库时区设置的影响，想象一下下一天有夏令时改变，会出现什么情况。

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

- Timo Huovinen

Answer 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  

    )

- Ovais Khatri

---

实际上那样做行不通。考虑一个开始时间在范围之前，但结束时间在范围内...这是一个重叠，但您的查询无法找到它。 - Bohemian

@Bohemian，这实际上是可行的：查询会检查两个范围，并且对于“其他”范围，查询实际上会在那种情况下给出结果。 - Jan M

Answer 4

你可以试试这个，对我来说很有效

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 
);

或者这个

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 

- Matas Lesinskas

Answer 5

在PHP中很简单。按照开始时间对列表进行排序，然后检查连续的条目是否有重叠。

$end[$k] > $start[$k+1]

在MySQL中的等效方法要复杂一些，除非你使用的是MySQL 8.0（或MariaDB 10.2），因为需要使用LEAD()或LAG()函数来查看相邻行。

- Rick James