含T的日期时间WHERE子句导致SQLite查询无返回数据问题

现象对比 #

以下查询无法返回任何数据：

SELECT "t0"."id" AS "res0", "t0"."time" AS "res1", "t1"."name" AS "res2", "t1"."description" AS "res3", "t1"."type" AS "res4"
FROM "menus" AS "t0"
INNER JOIN "meals" AS "t1"
INNER JOIN "meal_for_menus" AS "t2"
ON ("t2"."menu__id" = "t0"."id") AND ("t2"."meal__name" = "t1"."name")
WHERE "t0"."time" BETWEEN ('2024-08-11T00:00:00') AND ('2024-08-11T23:59:00');

而以下查询可正常返回数据：

SELECT "t0"."id" AS "res0", "t0"."time" AS "res1", "t1"."name" AS "res2", "t1"."description" AS "res3", "t1"."type" AS "res4"
FROM "menus" AS "t0"
INNER JOIN "meals" AS "t1"
INNER JOIN "meal_for_menus" AS "t2"
ON ("t2"."menu__id" = "t0"."id") AND ("t2"."meal__name" = "t1"."name")
WHERE "t0"."time" BETWEEN ('2024-08-11 00:00:00') AND ('2024-08-11 23:59:00');

两者唯一差异是第一个查询的时间字符串使用**"T"**作为日期和时间的分隔符，尽管SQLite官方文档说明支持ISO 8601格式（含"T"），但实际查询无结果。

背景 #

该查询由beam-sqlite自动生成，对应的Haskell代码如下：

todaysMenu :: Connection -> Day -> IO [(Menu, Meal)]
todaysMenu conn day =
  runBeamSqliteDebug putStrLn conn $
    runSelectReturningList $
      select $
        manyToMany_
          (_pacomerMealForMenus paComerDb)
          _mealformenuMenu
          _mealformenuMeal
          ( filter_
              ( \menu ->
                  between_
                    (_menuTime menu)
                    (val_ $ LocalTime day (TimeOfDay 0 0 0))
                    (val_ $ LocalTime day (TimeOfDay 23 59 0))
              )
              (all_ (_pacomerMenus paComerDb))
          )
          (all_ (_pacomerMeals paComerDb))

原因解析 #

1. 字符串字面量直接匹配：SQLite是弱类型数据库，如果menus.time字段存储为TEXT类型，且实际存储的时间格式是空格分隔的YYYY-MM-DD HH:MM:SS，那么含"T"的字符串会直接和存储值做字面量比较。由于"2024-08-11T00:00:00"和"2024-08-11 00:00:00"是完全不同的字符串，自然匹配不到。

2. beam-sqlite的序列化逻辑：beam-sqlite默认将LocalTime类型序列化为含"T"的ISO 8601格式字符串，但如果数据库中存储的时间格式是空格分隔，就会出现格式不匹配的问题。

3. 隐式日期转换未触发：SQLite仅在使用日期函数（如datetime()）包裹操作数时，才会尝试将字符串解析为时间值。而BETWEEN操作符在这里直接比较字段和字符串，没有触发隐式转换，因此只能按字符串规则匹配。

解决建议 #

• 统一存储格式：将menus.time字段的存储格式改为含"T"的ISO 8601格式，和beam生成的查询格式保持一致。
• 自定义beam序列化：修改beam-sqlite的LocalTime序列化逻辑，使其生成空格分隔的时间字符串，匹配数据库存储格式。
• 显式转换时间类型：在查询中用datetime()函数包裹时间字段和参数，强制SQLite按时间值比较，示例SQL：

  WHERE datetime("t0"."time") BETWEEN datetime('2024-08-11T00:00:00') AND datetime('2024-08-11T23:59:00');
  

  对应beam代码可通过sqlFunction_调用datetime函数：

  filter_ (\menu ->
    between_
      (sqlFunction_ "datetime" [_menuTime menu])
      (sqlFunction_ "datetime" [val_ $ LocalTime day (TimeOfDay 0 0 0)])
      (sqlFunction_ "datetime" [val_ $ LocalTime day (TimeOfDay 23 59 0)])
  )
  

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