如何用SQL查询检测SQLite表中的日期时间间隔缺失

可以利用SQLite的窗口函数LAG()获取每条记录的上一条时间，通过计算时间差找出间隔超过1分钟的情况，定位缺失的时间段。

核心查询语句 #

SELECT 
    prev_date AS 缺失开始时间,
    sampleDate AS 缺失结束时间
FROM (
    SELECT 
        sampleDate,
        LAG(sampleDate) OVER (ORDER BY sampleDate) AS prev_date
    FROM 你的表名
) AS sub
WHERE 
    prev_date IS NOT NULL
    AND (JULIANDAY(sampleDate) - JULIANDAY(prev_date)) * 1440 > 1;

逻辑说明 #

• LAG(sampleDate) OVER (ORDER BY sampleDate)：按时间顺序获取当前记录的上一条记录的sampleDate值。
• (JULIANDAY(sampleDate) - JULIANDAY(prev_date)) * 1440：将两个时间的天数差转换为分钟数，判断是否超过1分钟（即存在缺失）。
• 结果会成对展示缺失时间段的首尾时间点，清晰标记出中断区间。

如果需要按你给出的示例格式，每行输出一个时间点，可以用以下查询：

SELECT prev_date FROM (
    SELECT 
        sampleDate,
        LAG(sampleDate) OVER (ORDER BY sampleDate) AS prev_date
    FROM 你的表名
) AS sub
WHERE 
    prev_date IS NOT NULL
    AND (JULIANDAY(sampleDate) - JULIANDAY(prev_date)) * 1440 > 1
UNION ALL
SELECT sampleDate FROM (
    SELECT 
        sampleDate,
        LAG(sampleDate) OVER (ORDER BY sampleDate) AS prev_date
    FROM 你的表名
) AS sub
WHERE 
    prev_date IS NOT NULL
    AND (JULIANDAY(sampleDate) - JULIANDAY(prev_date)) * 1440 > 1
ORDER BY 1;

注意事项 #

• 替换查询中的你的表名为实际表名称。
• 确保sampleDate列存储的是YYYY-MM-DD HH:MM:SS格式的字符串或DATETIME类型，否则JULIANDAY函数无法正确计算时间差。

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