The following query fails:

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');

but the following query works:

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');

The difference is the “T” in the datetimes from the first query. I see “T” is supported by SQLite so I don’t understand why the query is failing.

FWIW, this query was generated by beam-sqlite.

I’ll try to execute a raw query but I wish I could keep the query generated by beam:

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))