基于星期的PostgreSQL时间区间重叠检测实现方案

核心思路 #

把所有时间转换为以周为单位的总秒数（一周总秒数为 7*24*3600=604800），将每个告警规则的时间区间转化为周秒数范围内的区间，通过数值区间重叠判断处理跨周场景。

直接SQL查询（返回冲突ID） #

输入参数：p_day_of_week（0-6）、p_seconds_after_day_start、p_duration_minutes

WITH new_rule AS (
    SELECT
        p_day_of_week * 86400 + p_seconds_after_day_start AS start_week_sec,
        (p_day_of_week * 86400 + p_seconds_after_day_start + p_duration_minutes * 60) AS end_week_sec
)
SELECT id
FROM alert_conditions ac, new_rule nr
WHERE
    -- 场景1：新规则与已有规则均不跨周，区间重叠
    (nr.end_week_sec <= 604800 AND 
     ac.start_week_sec < nr.end_week_sec AND ac.end_week_sec > nr.start_week_sec)
    OR
    -- 场景2：新规则跨周，检查与所有已有规则的重叠
    (nr.end_week_sec > 604800 AND 
     (
         -- 新规则本周段与已有规则重叠
         ac.end_week_sec > nr.start_week_sec
         OR
         -- 新规则下周段与已有规则重叠
         ac.start_week_sec < (nr.end_week_sec - 604800)
         OR
         -- 已有规则本身跨周，必然与跨周新规则重叠
         ac.end_week_sec > 604800
     ))
    OR
    -- 场景3：已有规则跨周，新规则不跨周，区间重叠
    (nr.end_week_sec <= 604800 AND 
     ac.end_week_sec > 604800 AND
     (ac.start_week_sec < nr.end_week_sec OR (ac.end_week_sec - 604800) > nr.start_week_sec));

可复用存储过程 #

如果需要多次调用，可创建PL/pgSQL存储过程：

CREATE OR REPLACE FUNCTION find_conflicting_alerts(
    p_day_of_week integer,
    p_seconds_after_day_start integer,
    p_duration_minutes integer
) RETURNS SETOF integer AS $$
DECLARE
    start_week_sec integer;
    end_week_sec integer;
BEGIN
    start_week_sec := p_day_of_week * 86400 + p_seconds_after_day_start;
    end_week_sec := start_week_sec + p_duration_minutes * 60;

    RETURN QUERY
    SELECT id
    FROM alert_conditions ac
    WHERE
        (end_week_sec <= 604800 AND 
         ac.start_week_sec < end_week_sec AND ac.end_week_sec > start_week_sec)
        OR
        (end_week_sec > 604800 AND 
         (ac.end_week_sec > start_week_sec OR ac.start_week_sec < (end_week_sec - 604800) OR ac.end_week_sec > 604800))
        OR
        (end_week_sec <= 604800 AND 
         ac.end_week_sec > 604800 AND
         (ac.start_week_sec < end_week_sec OR (ac.end_week_sec - 604800) > start_week_sec));
END;
$$ LANGUAGE plpgsql STABLE;

调用方式：

SELECT * FROM find_conflicting_alerts(6, 82800, 240); -- 示例：周日23点开始，持续4小时

性能优化建议 #

为了加速查询，建议给表添加计算列和索引：

1. 添加存储计算列：

ALTER TABLE alert_conditions 
ADD COLUMN start_week_sec integer GENERATED ALWAYS AS (day_of_week * 86400 + seconds_after_day_start) STORED;

ALTER TABLE alert_conditions 
ADD COLUMN end_week_sec integer GENERATED ALWAYS AS (start_week_sec + duration_minutes * 60) STORED;

2. 创建复合索引：

CREATE INDEX idx_alert_week_time_range ON alert_conditions (start_week_sec, end_week_sec);

这样查询时PostgreSQL可以直接利用索引过滤数据，避免全表扫描。

内容的提问来源于stack exchange，提问作者Roman Pushkin