Tortoise-orm: Filtering by datetime in SQLite

[mheppner]

Describe the bug
Filtering by datetime does not appear to work correctly with SQLite.

To Reproduce

class MyModel(Model):
    time = fields.DatetimeField()

dt = datetime.datetime.now()

await MyModel.create(time=dt)
entry = await MyModel.get(time=dt)  # produces empty queryset

Expected behavior
I don't typically use SQLite, so I'm not sure if this is a tortoise-orm thing or a SQLite thing. The query that's logged is:

SELECT "time","id" FROM "mymodel" WHERE "time"='2019-01-01T12:00:00' LIMIT 2;

Running queries manually, I can get these to work as expected:

SELECT "time","id" FROM "mymodel" WHERE "time"='2019-01-01 12:00:00' LIMIT 2;
SELECT "time","id" FROM "mymodel" WHERE "time"=DATETIME('2019-01-01T12:00:00') LIMIT 2;

Basically, it doesn't seem to filter properly since the datetime object is being serialized into a string with the "T" in it, unless the string value is wrapped in DATETIME().

I'm currently working around this by filtering without the "T" using datetime.datetime.strftime(dt, '%Y-%m-%d %H:%M:%S'). I have not tested this in other database backends.

Also unrelated, but why does .get() result in a LIMIT 2?

Additional context

• python 3.7.3
• sqlite3 3.24.0
• tortoise-orm==0.11.9
• aiosqlite==0.10.0

[grigi]

Hi, thank you for logging the issue.

I'm not surprised that SQLite has a weirdness for datetimes, considering it is an emulated format.
The T is part of the ISO8601 timestamp format (generated by datetime.datetime.now().isoformat())that I was under the impression that SQLite was using, but it appears that it does not always use it (or it could depend on locale, not sure).

Also the LIMIT 2 for get is to help validate we get exactly a single parameter. If get get more than one (e.g. two), then an exception is raised, as is getting no value. the 2 value is an optimization to minimise the dataset so that we don't accidentally get a hundred values, etc...

Back to the timestamp issue, thank you for this test case, I'll add it as a test case, confirm replication of the issue, and then should be able to confidently fix it.
Reading through the SQLite docs https://sqlite.org/lang_datefunc.html the datetime string with/without the 'T' should be equivalent, but obviously is not...

[grigi]

Ah, it appears to be a case of the sqlite library handling formatting for a parametrised query like so:
The datetime() function returns "YYYY-MM-DD HH:MM:SS".

Then stores it as a string, and when we do a search the value is a string value comparison that then fails. If we change the formatting to be identical to what SQLites datetime() function is, it should work.

[grigi]

Please test if v0.11.10 resolves it for you?

[mheppner]

The latest version seems to work as expected. Thanks for the thorough explanation and the quick fix! Loving this library so far!