Search code examples
pythonsqlitetimezonetortoise-orm

Filtering by datetime fails

I am using Python and Tortoise ORM to manage my SQLite database. I have a User model defined that looks like so:

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

I tried to query the User model by datetime field and everytime I did so I got an empty list. Here's the initialization and query code:

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

It outputs this:

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
[]

Clearly, it is not the expected output because I created users 5 seconds before and ORM didn't return them to me.
Can anyone explain what am I doing wrong? I tried setting timezone and use_tz parameters in Tortoise.init(). Got no results. Here's the SQL query that ORM generates:

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'

UPD:

Based on @winner_joiner answer, this query was not returning any users because of sqlite bug. I tried using the same query on a PosgreSQL database and it worked flawlessly. Screenshot is attached: docker logs

Solution

  • I'm no expert for tortoise-orm, but here is a link to an closed issue of tortoise-orm which addresses, an similar issue.

    It issue could have to do with the versions you are using (sqlite, tortoise-orm, ... ), or simply that the bug wasn't completely fixed. Since the generate SQL returns no results, but if you remove the "T", from the time portion of the WHERE clause, as mentioned in the GitHub Issue, the entries are returned.

    I only tested the sqlite (version 3.47.0) part/queries.