首頁>Program>source

我有一个包含以下欄位的MySQL表:

  • name
  • starttime
  • endtime

starttimeendtime 是MySQL TIME 欄位(不是 DATETIME ).我需要一種定期"掃描"表以查看表中時間範圍是否重叠的方法.如果有来自 10:00-11:00的活動 另一个来自 10:30-11:30 ,我想提醒時間重叠。

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

我將使用PHP執行此操作。

最新回復
  • 5月前
    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;
    

  • 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
    

  • c#:DataGrid获取選定行的列值
  • Maven和JSF webapp結構,確切地放置JSF資源