如何查询过去三周同一日期同一时间戳的指定列数据？

方案一：使用UNION ALL合并多时间段查询 #

这种方法直观易懂，适合固定需要过去三周数据的场景，完全对齐你原查询的时间窗口逻辑：

WITH rc_pt AS (
    -- 三周前对应时间段的数据
    SELECT
        timestamp,
        rc_id,
        pt_id,
        impacted_users,
        '3 weeks ago' AS period
    FROM pt_rc
    WHERE timestamp BETWEEN '2022-10-28 11:00:00'::timestamp - INTERVAL '21 days 1 hour'
                        AND '2022-10-28 11:00:00'::timestamp - INTERVAL '20 days 23 hours'
    UNION ALL
    -- 两周前对应时间段的数据
    SELECT
        timestamp,
        rc_id,
        pt_id,
        impacted_users,
        '2 weeks ago' AS period
    FROM pt_rc
    WHERE timestamp BETWEEN '2022-10-28 11:00:00'::timestamp - INTERVAL '14 days 1 hour'
                        AND '2022-10-28 11:00:00'::timestamp - INTERVAL '13 days 23 hours'
    UNION ALL
    -- 一周前对应时间段的数据
    SELECT
        timestamp,
        rc_id,
        pt_id,
        impacted_users,
        '1 week ago' AS period
    FROM pt_rc
    WHERE timestamp BETWEEN '2022-10-28 11:00:00'::timestamp - INTERVAL '7 days 1 hour'
                        AND '2022-10-28 11:00:00'::timestamp - INTERVAL '6 days 23 hours'
)
SELECT DISTINCT timestamp, rc_id, pt_id, impacted_users, period
FROM rc_pt;

方案二：用generate_series动态生成时间范围（更灵活） #

如果后续需要扩展到更多周，这种方法只需修改参数即可，代码更简洁：

WITH target AS (
    SELECT '2022-10-28 11:00:00'::timestamp AS target_time
),
week_offsets AS (
    SELECT generate_series(1, 3) * 7 AS days_offset -- 生成7、14、21天的偏移量
),
time_ranges AS (
    SELECT
        target_time - (days_offset || ' days')::interval - '1 hour'::interval AS start_time,
        target_time - (days_offset || ' days')::interval + '1 hour'::interval AS end_time,
        (days_offset / 7) || ' weeks ago' AS period
    FROM target, week_offsets
)
SELECT DISTINCT
    pr.timestamp,
    pr.rc_id,
    pr.pt_id,
    pr.impacted_users,
    tr.period
FROM pt_rc pr
JOIN time_ranges tr ON pr.timestamp BETWEEN tr.start_time AND tr.end_time;

方案三：通过date_part筛选星期几与时间范围 #

如果你想直接匹配同一星期几和10:00-12:00的时间区间，而不需要计算具体日期，可以用这种方法：

WITH target AS (
    SELECT '2022-10-28 11:00:00'::timestamp AS target_time
)
SELECT DISTINCT
    timestamp,
    rc_id,
    pt_id,
    impacted_users,
    FLOOR((EXTRACT(EPOCH FROM (target_time - timestamp)) / 86400) / 7) || ' weeks ago' AS period
FROM pt_rc, target
WHERE
    -- 匹配10:00到12:00的时间范围
    timestamp::time BETWEEN '10:00:00' AND '12:00:00'
    -- 匹配与目标日期相同的星期几（DOW：周日=0，周六=6）
    EXTRACT(DOW FROM timestamp) = EXTRACT(DOW FROM target_time)
    -- 限制在过去三周内
    timestamp >= target_time - INTERVAL '21 days'
    AND timestamp < target_time;

说明 #

• 三个方案都保留了你原查询中的DISTINCT逻辑，避免重复数据。
• 时间窗口完全对齐你原查询的逻辑：目标时间点前后1小时的区间，对应过去三周的每周同一天。

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