Checking a table for time overlap?
I have a MySQL table with the following fields:
- name
- starttime
- endtime
starttime
and endtime
are MySQL TIME
fields (not DATETIME
). I nee开发者_运维百科d 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.
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;
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
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.
If your database is using UTC timezone (as it should) then you can use this
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;
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
)
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
精彩评论