C#查询SQLite的DateTime字段计算相邻记录时间差及对比异常问题

实现方案 #

SQLite 3.25.0及以上版本支持LAG窗口函数，可以直接在查询层完成相邻记录时间差计算，步骤如下：

1. 先将你存储的MM-dd-yyyy HH:mm:ss格式的时间字符串转换为SQLite可识别的时间格式
2. 对时间字段升序排序，用LAG函数取出上一行的时间值
3. 用JULIANDAY计算两个时间的差值，乘以24得到小时单位的差值

示例查询语句：

SELECT 
    time,
    -- 第一条记录没有上一行，差值可按需求设默认值，此处匹配你的示例填1
    CASE WHEN LAG(STRFTIME('%Y-%m-%d %H:%M:%S', time, '%m-%d-%Y %H:%M:%S')) OVER (ORDER BY time) IS NULL 
         THEN 1
         ELSE ROUND((JULIANDAY(STRFTIME('%Y-%m-%d %H:%M:%S', time, '%m-%d-%Y %H:%M:%S')) - JULIANDAY(LAG(STRFTIME('%Y-%m-%d %H:%M:%S', time, '%m-%d-%Y %H:%M:%S')) OVER (ORDER BY time))) * 24, 2) 
    END AS `difference (hour)`
FROM (
    -- 替换为你原来的筛选逻辑，必须按时间升序排序
    SELECT time FROM LastRun 
    WHERE LastUpdateTime > @queryDate
    ORDER BY time ASC
) t

注意事项 #

• 建议把SQL字符串拼接改成参数化查询，既避免SQL注入风险，也不会出现时间格式转义异常
• 注意你原有筛选代码里变量名不一致，定义的变量是date，查询语句里用的是queryDate，避免运行时报错
• 如果你的SQLite版本低于3.25.0不支持窗口函数，可以直接在业务代码层遍历计算差值，逻辑和窗口函数一致

根因分析 #

代码的变量赋值顺序错误：你先执行了prev = thisDateTime;把当前时间赋值给了prev变量，之后再执行var d = thisDateTime - prev.Value;，两个变量值完全相等，差值必然是00:00:00。

修复方案 #

调整代码执行顺序，先计算差值，再更新prev的值：

// 初始化prev为null，第一条记录跳过差值计算
DateTime? prev = null;
// 固定间隔可以提到循环外，无需重复初始化
TimeSpan fixTimeDiff = new TimeSpan(0, 10, 0);
while(reader.Read())
{
    DateTime thisDateTime = reader.GetDateTime(reader.GetOrdinal("time"));
    // 非第一条记录时计算差值
    if (prev.HasValue)
    {
        var d = thisDateTime - prev.Value;
        int compare = TimeSpan.Compare(fixTimeDiff, d);
        Console.WriteLine("{0} {1} {2} ( Compare should return {3} )", 
        fixTimeDiff, compare == 1 ? ">" : compare == 0 ? "=" : "<", d, 
        compare);
    }
    // 差值计算完成后，再更新prev为当前时间
    prev = thisDateTime;
}

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