Opened 6 years ago
Closed 6 years ago
#29767 closed Bug (fixed)
Failed to cast between datetime and date on SQLite
Reported by: | Rémy Hubscher | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 2.1 |
Severity: | Normal | Keywords: | sqlite |
Cc: | Triage Stage: | Ready for checkin | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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
Change History (8)
comment:1 by , 6 years ago
Component: | Uncategorized → Database layer (models, ORM) |
---|---|
Type: | Uncategorized → Bug |
comment:2 by , 6 years ago
Keywords: | sqlite added |
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:4 by , 6 years ago
Has patch: | set |
---|---|
Patch needs improvement: | set |
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).
comment:5 by , 6 years ago
Patch needs improvement: | unset |
---|
I updated the PR by adding a database feature for the precision loss of SQLite when casting to datetime.
comment:6 by , 6 years ago
Summary: | Django ORM: Cast between datetime and date → Failed to cast between datetime and date on SQLite |
---|
comment:7 by , 6 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
After a slight modification of the provided tests, I can confirm that they pass with PostgreSQL and MySQL, but not on SQLite.