使用Tortoise ORM基于datetime字段过滤数据失败问题排查

我使用Python结合Tortoise ORM管理SQLite数据库，定义了如下User模型：

class User(Model):
    id = tortoise.fields.IntField(primary_key=True)
    age = tortoise.fields.IntField()
    datetime = tortoise.fields.DatetimeField(auto_now_add=True)

尝试通过datetime字段的范围条件查询User模型时，每次都返回空列表。初始化及查询代码如下：

import asyncio
import time
import tortoise.connection
import tortoise.timezone

from tortoise import Tortoise
from database import User
from datetime import timedelta


async def test():
    for _ in range(5):
        user = await User.create(age=18)
        print(f"Created user with datetime: {user.datetime}")

    time.sleep(5)
    end_time = tortoise.timezone.now()
    start_time = end_time - timedelta(hours=2)

    print(f"Start time is {start_time}")
    print(f"End time is {end_time}")
    users = await User.filter(datetime__range=(start_time, end_time)).all()
    print(users)


async def main():
    await Tortoise.init(
        db_url="sqlite://:memory:",
        # timezone="Europe/Moscow",
        # use_tz=True,
        modules={"discord": ["database"]}
    )
    await Tortoise.generate_schemas()
    await test()


if __name__ == "__main__":
    try:
        asyncio.run(main())
    finally:
        asyncio.run(tortoise.connection.connections.close_all())

程序运行输出：

Created user with datetime: 2024-09-21 16:57:31.550512+00:00
Created user with datetime: 2024-09-21 16:57:31.550512+00:00
Created user with datetime: 2024-09-21 16:57:31.550512+00:00
Created user with datetime: 2024-09-21 16:57:31.551512+00:00
Created user with datetime: 2024-09-21 16:57:31.551512+00:00
Start time is 2024-09-21 14:57:36.552198+00:00
End time is 2024-09-21 16:57:36.552198+00:00
[]

显然结果不符合预期——5秒前刚创建了用户，但ORM查询却返回空列表。我尝试在Tortoise.init()中设置timezone和use_tz参数，问题依旧存在。ORM生成的SQL查询语句如下：

SELECT "datetime","age","id"
FROM "user"
WHERE "datetime" BETWEEN '2024-09-21T14:57:36.552198+00:00' AND '2024-09-21T16:57:36.552198+00:00'

更新说明 #

根据@winner_joiner的解答，该查询无结果是SQLite的bug导致的。我在PostgreSQL数据库上执行相同逻辑的查询，成功返回了创建的用户记录。

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