查询时间区间内定时插入的缺失记录方案求助

核心思路是：先生成指定时间范围内所有应该存在的时间点（每小时的14、29、44、59分），再通过与现有数据做差集，筛选出未出现的时间点。以下是不同数据库环境下的实现示例：

1. MySQL 8.0+ 实现 #

WITH RECURSIVE hours AS (
    -- 自动获取数据中的时间范围边界，也可手动替换为指定起始/结束小时
    SELECT DATE_FORMAT(MIN(DateTime), '%Y-%m-%d %H:00:00') AS hour_start
    FROM your_table
    UNION ALL
    SELECT DATE_ADD(hour_start, INTERVAL 1 HOUR)
    FROM hours
    WHERE hour_start < (SELECT DATE_FORMAT(MAX(DateTime), '%Y-%m-%d %H:00:00') FROM your_table)
),
fixed_minutes AS (
    SELECT 14 AS minute UNION ALL
    SELECT 29 UNION ALL
    SELECT 44 UNION ALL
    SELECT 59
),
expected_times AS (
    SELECT STR_TO_DATE(CONCAT(h.hour_start, ':', f.minute), '%Y-%m-%d %H:%i:%s') AS expected_datetime
    FROM hours h
    CROSS JOIN fixed_minutes f
)
SELECT e.expected_datetime AS DateTime
FROM expected_times e
LEFT JOIN your_table t ON e.expected_datetime = t.DateTime
WHERE t.DateTime IS NULL
-- 可选：手动指定时间范围过滤
-- AND e.expected_datetime BETWEEN '2023-05-01 06:00:00' AND '2023-05-01 08:59:59'
ORDER BY e.expected_datetime;

2. PostgreSQL 实现 #

WITH fixed_minutes AS (
    SELECT unnest(ARRAY[14,29,44,59]) AS minute
),
expected_times AS (
    SELECT generate_series(
        DATE_TRUNC('hour', MIN(DateTime)),
        DATE_TRUNC('hour', MAX(DateTime)),
        INTERVAL '1 hour'
    ) + (minute || ' minutes')::INTERVAL AS expected_datetime
    FROM your_table, fixed_minutes
)
SELECT e.expected_datetime AS DateTime
FROM expected_times e
LEFT JOIN your_table t ON e.expected_datetime = t.DateTime
WHERE t.DateTime IS NULL
ORDER BY e.expected_datetime;

关键说明 #

• 替换代码中的your_table为实际表名，确保DateTime字段类型与生成的expected_datetime一致（避免因时间精度、格式差异导致匹配失败）
• 若需指定固定时间范围，直接修改生成时间序列的起始/结束参数，或在WHERE子句中添加过滤条件

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