如何修改SQL查询以支持跨多天的重复日历事件？

问题分析 #

原查询仅针对起止日期相同的重复事件做判断，未考虑事件本身跨多天的场景。比如事件起始于2024-02-19、结束于2024-02-22（持续4天），按5天重复时，2024-02-26、27号属于该事件的重复周期，但原查询无法识别这种跨天的重叠关系。

核心问题是：原逻辑只检查了查询日期是否符合重复间隔规则，未验证查询日期是否落在某个重复周期的事件时间段内。

修改后的SQL查询 #

SET @query_start = '2024-02-26 00:00:00';
SET @query_end = '2024-02-26 23:59:59';

SELECT DISTINCT ce.*
FROM calendar_events ce
LEFT JOIN calendar_event_repetitions cer ON ce.id = cer.calendar_event_id
WHERE ce.user_id = '837717ff-4746-4451-9986-c5529d671c52' 
AND (
    -- 规则1：原始事件时间段与查询日期直接重叠
    (ce.start_date <= @query_end AND ce.end_date >= @query_start)
    OR (
        -- 规则2：重复事件逻辑，仅处理天级重复且无周规则的情况
        cer.interval IS NOT NULL 
        AND cer.interval_type = 'd' 
        AND cer.weekly_days IS NULL
        AND (cer.ends_at IS NULL OR cer.ends_at >= ce.start_date)
        AND (
            -- 子规则A：查询日期落在某个重复周期的时间段内
            EXISTS (
                SELECT 1
                WHERE
                    -- 计算当前查询周期内，最近的不晚于查询结束日的重复起始日期
                    DATE_ADD(ce.start_date, INTERVAL FLOOR(TIMESTAMPDIFF(DAY, ce.start_date, @query_end)/cer.interval)*cer.interval DAY) <= @query_end
                    -- 该重复周期的结束日期（起始日+事件持续天数）晚于查询开始日
                    AND DATE_ADD(
                        ce.start_date, 
                        INTERVAL (FLOOR(TIMESTAMPDIFF(DAY, ce.start_date, @query_end)/cer.interval)*cer.interval + TIMESTAMPDIFF(DAY, ce.start_date, ce.end_date) + 1) DAY
                    ) > @query_start
                    -- 重复起始日期不超过重复结束时间（如果有）
                    AND (cer.ends_at IS NULL OR DATE_ADD(ce.start_date, INTERVAL FLOOR(TIMESTAMPDIFF(DAY, ce.start_date, @query_end)/cer.interval)*cer.interval DAY) <= cer.ends_at)
            )
            OR
            -- 子规则B：查询日期落在重复周期的中间天数（非起始日但属于事件时间段）
            EXISTS (
                SELECT 1
                WHERE
                    -- 查询开始日早于下一个重复起始日
                    @query_start < DATE_ADD(ce.start_date, INTERVAL CEIL(TIMESTAMPDIFF(DAY, ce.start_date, @query_start)/cer.interval)*cer.interval DAY)
                    -- 查询开始日晚于当前重复周期的起始日，且早于该周期的结束日
                    AND DATE_ADD(
                        ce.start_date, 
                        INTERVAL (FLOOR(TIMESTAMPDIFF(DAY, ce.start_date, @query_start)/cer.interval)*cer.interval + TIMESTAMPDIFF(DAY, ce.start_date, ce.end_date) + 1) DAY
                    ) > @query_start
                    -- 重复起始日期不超过重复结束时间（如果有）
                    AND (cer.ends_at IS NULL OR DATE_ADD(ce.start_date, INTERVAL FLOOR(TIMESTAMPDIFF(DAY, ce.start_date, @query_start)/cer.interval)*cer.interval DAY) <= cer.ends_at)
            )
        )
    )
);

关键逻辑说明 #

1. 事件持续天数计算：通过TIMESTAMPDIFF(DAY, ce.start_date, ce.end_date) + 1获取事件包含首尾的总天数（比如2024-02-19到2024-02-22为4天）。
2. 重复周期重叠判断：
   • 计算查询日期范围内是否存在某个重复起始日，其对应的事件时间段（起始日+持续天数）与查询日期有交集。
   • 同时处理两种场景：查询日期是重复周期的起始日，或是周期内的中间天数。
3. 边界控制：确保重复起始日期不超过cer.ends_at（如果设置了重复结束时间）。

以你给出的例子验证：

• 原事件：2024-02-19 ~ 2024-02-22（持续4天），间隔5天重复。
• 第一个重复周期起始日为2024-02-24，对应的事件时间段是2024-02-24 ~ 2024-02-27。
• 查询2024-02-26、27号时，该时间段与查询日期重叠，符合返回条件；2024-02-28号不在任何重复周期的时间段内，不会返回。

内容的提问来源于stack exchange，提问作者Stanislau Karaliou