Opened 6 years ago

Closed 5 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 Claude Paroz, 6 years ago

Component: UncategorizedDatabase layer (models, ORM)
Type: UncategorizedBug

comment:2 by Claude Paroz, 6 years ago

Keywords: sqlite added
Triage Stage: UnreviewedAccepted

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

comment:3 by Claude Paroz, 6 years ago

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

comment:4 by Claude Paroz, 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 Claude Paroz, 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 Claude Paroz, 6 years ago

Summary: Django ORM: Cast between datetime and dateFailed to cast between datetime and date on SQLite

comment:7 by Tim Graham, 6 years ago

Triage Stage: AcceptedReady for checkin

comment:8 by Claude Paroz <claude@…>, 5 years ago

Resolution: fixed
Status: newclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top