Failed to cast between datetime and date on SQLite

[Rémy Hubscher]

While using with Annotations and Cast I realized that the casting between datetime and date with SQLite3 was kinda broken.

Cast("datetime_field", DateField())

is transformed into

CAST("datetime_field" AS TEXT)

rather than for instance

substr(CAST(datetime_field AS TEXT),0,11)

I was able to use extra to fix that and run the query. However I would like to fix this.

This is especially useful for my usecase where I want to GROUP BY the annotated value.

I wrote a test for it ​https://github.com/django/django/pull/10387/files

[Claude Paroz]

After a slight modification of the provided tests, I can confirm that they pass with PostgreSQL and MySQL, but not on SQLite.

[Claude Paroz]

Note that we should also test/handle casts to time and datetime.

[Claude Paroz]

In ​this PR, the test db_functions.comparison.test_cast.CastTests.test_cast_from_python_to_datetime fails because casting a Python datetime to a SQLite3 datetime is rounding the fractional seconds to the first 3 digits.

Now I'm not sure if we should fix this or not, and if we should, if this can be fixed on SQLite. Casting a datetime to a datetime seems a bit strange. Generally, when you cast to datetime, you have a type with less precision (typically a date).

[Claude Paroz]

I updated the PR by adding a database feature for the precision loss of SQLite when casting to datetime.

[Claude Paroz <claude@…>]

In fc3a4630:

Fixed #29767 -- Made date-related casts work on SQLite

Thanks Rémy Hubscher for the report and Tim Graham and Simon Charette for the reviews.