SQL时区转换查询测试异常问题排查求助

首先，我梳理了你遇到的核心问题：第二个测试插入了已支付订单，但查询统计结果始终为0，同时reader.HasRows返回true却没有正确统计数据。结合你的代码，我找到了几个关键问题点和对应的修复方案：

1. SQL语句中的HTML实体导致条件完全无效 #

这是最可能导致结果为0的原因：你在SqlGetOrderStats字符串中使用了HTML转义字符>=和<=，而非SQL原生的比较运算符>=和<=。MySQL无法识别这些HTML实体，会将其解析为无效的表达式，导致WHERE条件永远不成立，自然查不到任何数据。

修复方案：
替换HTML实体为SQL运算符，同时注意order是MySQL关键字，需要用反引号包裹：

private const string SqlGetOrderStats = "SELECT count(id) as orderNum, sum(total) as Total " +
                                        "FROM `order` " +  // 用反引号包裹关键字表名
                                        "WHERE Date(CONVERT_TZ(OrderDate,'US/Eastern','US/Pacific')) >= @STARTDATE " +
                                        "AND Date(CONVERT_TZ(OrderDate,'US/Eastern','US/Pacific')) <= @ENDDATE " +
                                        "AND Deleted = 0 AND paid = 1";

2. 时区转换与查询参数不匹配 #

你的测试逻辑和SQL转换逻辑存在时区错位：

• SQL中是将OrderDate从EST转换为PST后，再与日期参数比较
• 但测试NonPaidOrdersNotIncluded中，传递给GetOrderStats的是EST时区的日期范围
• 假设插入的OrderDate是EST当天，转换为PST后日期会提前一天，导致不在查询的EST日期范围内

修复方案：
传递与SQL转换结果一致的PST时区日期范围，同时确保插入的OrderDate是EST时区时间：

[Test]
public void NonPaidOrdersNotIncluded()
{
    AddOrder(orderTotal: 100, paid: false);
    AddOrder(orderTotal: 100, paid: true);
    var db = new DbAccountingStatistics();
    var stats = db.GetOrderStats(
        TimeZoneInfo.ConvertTime(TodayStartOrDay, Pst),  // 传递PST时区的开始时间
        TimeZoneInfo.ConvertTime(TodayEndOfDay, Pst)     // 传递PST时区的结束时间
    );
    Assert.That(stats.NumberofOrders, Is.EqualTo(1));
    Assert.That(stats.TotalOrders, Is.EqualTo(100));
}

// 修正AddOrder中的OrderDate，确保存储EST时区时间
private void AddOrder(decimal orderTotal, bool paid)
{
    var db = new DbOrder();
    var id = db.Insert(new PocoOrder
    {
        Paid = paid,
        Total = orderTotal,
        OrderDate = TimeZoneInfo.ConvertTime(DateTime.Today, Est)  // 存储EST时间，匹配SQL转换逻辑
    });
    _logging.FailIfAnyLoggedErrors();
    _dbActions.AddSimpleChange($"delete from `order` where id = {id}");  // 同样用反引号包裹表名
}

3. 额外验证点 #

• 确认MySQL时区功能正常：在MySQL客户端执行SELECT CONVERT_TZ('2024-05-20 00:00:00', 'US/Eastern', 'US/Pacific');，验证是否返回正确的PST时间（应为2024-05-19 21:00:00）
• 确认Deleted字段默认值：检查PocoOrder的Deleted属性是否默认设置为0，如果默认是1，会被WHERE条件过滤掉
• 验证数据插入即时性：确保DbOrder.Insert方法是立即将数据提交到数据库，而非延迟到SubmitChanges执行（因为测试中查询是在插入后立即进行的）

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