检查表是否存在时间重叠? [英] Checking a table for time overlap?

问题描述

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

I have a MySQL table with the following fields:

• 名称
• 开始时间
• 结束时间

starttime和endtime是MySQL TIME字段(不是DATETIME).我需要一种定期扫描"表以查看表中时间范围是否重叠的方法.如果有一个来自10:00-11:00的事件，另一个是来自10:30-11:30的事件，我希望收到时间重叠的提醒.

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.

真的没什么好想的，我只想知道是否存在重叠.

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

我将使用PHP执行此操作.

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

推荐答案

这是我很多年前找到答案的查询模式:

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;

这篇关于检查表是否存在时间重叠?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！