The issue you’re encountering with the SQLite query involving the "T" in datetime strings is likely due to how SQLite interprets date and time formats in comparison with the expected formats.

Understanding SQLite Date and Time Formats

SQLite supports several date and time formats, including ISO 8601 (YYYY-MM-DDTHH:MM:SS). However, its support might vary depending on the version and the context in which it is used. The two formats you are using are:

1. ISO 8601 Format with “T” ('2024-08-11T00:00:00'): This format is valid in SQLite, but if you’re encountering issues, it may be due to how SQLite or the SQL engine you’re using interprets or processes this format in certain contexts.

2. Space-Separated Format ('2024-08-11 00:00:00'): This format is widely accepted by SQLite for date and time values and is more universally supported in various SQL engines.

Why the Query Might Fail

1. Parsing Issues: SQLite may have issues parsing the ISO 8601 format with “T” in some contexts, especially if the SQL engine or ORM (like Beam) has its own expectations for date formats.

2. Version Differences: Different SQLite versions might handle date formats differently. Ensure you are using a version that supports the ISO 8601 format correctly.

3. SQL Engine or ORM Constraints: The SQL engine or ORM you’re using (in this case, Beam) might have constraints or bugs related to handling ISO 8601 formats.

Solutions

1. Use Space-Separated Format

Since the space-separated format works, it’s generally a good practice to use it for compatibility:

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

2. Convert ISO 8601 Format to Space-Separated Format

If you prefer using ISO 8601 format, you could try converting it to the space-separated format in your query or code:

-- Convert ISO 8601 to space-separated format
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 (strftime('%Y-%m-%d %H:%M:%S', '2024-08-11T00:00:00')) AND (strftime('%Y-%m-%d %H:%M:%S', '2024-08-11T23:59:00'));

3. Adjust the Beam-SQLITE Query

Modify the todaysMenu function to use the space-separated format:

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

Ensure that the date-time values being generated by LocalTime are in a format that SQLite can handle correctly.