我有一个包含以下欄位的MySQL表:
- name
- starttime
- endtime
starttime
和
endtime
是MySQL
TIME
欄位(不是
DATETIME
).我需要一種定期"掃描"表以查看表中時間範圍是否重叠的方法.如果有来自
10:00-11:00
的活動
另一个来自
10:30-11:30
,我想提醒時間重叠。
真的没什麼好想的,我只想知道是否存在重叠.
我將使用PHP執行此操作。
最新回復
- 5月前1 #
- 5月前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;
- 5月前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 )
- 5月前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
相似問題
- sql:MySQL SELECT DISTINCT多列mysqlsqldistinct2021-01-11 21:26
- sql:如何删除MySQL表中的重複項sqlmysqldatabase2021-01-11 03:23
- mysql:使用SQL計算型別時間的总和sqlmysql2021-01-11 02:27
- mysql:6000万个條目,請从特定月份選擇條目如何優化資料庫?mysqlsqlqueryoptimization2021-01-10 19:26
- sql:如何在MySQL中生成資料?mysqlsql2021-01-10 18:57
這是我很多年前找到答案的查詢模式:
要查詢"任何重叠",您可以將時間範圍的相反两端相互比较.我不得不掏出笔和纸,画出相邻的範圍,以了解邊缘情况归結為這種比较。
如果要防止任何行重叠,請在觸發器中放置此查詢的變體: