Use database adapters for converting time zone aware datetimes in raw queries
|Reported by:||akaariai||Owned by:||aaugustin|
|Component:||Database layer (models, ORM)||Version:||1.4-beta-1|
|Has patch:||no||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
The issue is that if you use timezone aware datetimes in raw SQL queries the datetimes aren't converted to UTC. This will give wrong results, as times are in UTC in the database, but the query parameters aren't. This issue is present at least on SQLite3 and MySQL. Oracle might suffer from this issue, too, but I can't verify that currently. The issues is present only when USE_TZ=True.
There has been some discussion about this both on django-developers and in ticket #17728. The conclusion seems to be that while the ORM is safe from these issues, the current behavior isn't acceptable for raw SQL users.
The fix for this would be using the adapter interfaces different databases offer. MySQL has a "converters" dictionary, and SQLite has register_adapter. These databases should be somewhat straightforward to support. Oracle seems to have a class based implementation for adapters, but I haven't studied it enough to say anything more about it.
I think the adapter should raise a warning when non-aware datetime is given as a parameter, and convert aware datetimes to UTC. Basically do what value_to_db_datetime does.
I am marking this as a release blocker, as some resolution (even if that would be wontfix) should be done before 1.4 release.
Change History (7)
comment:1 Changed 2 years ago by aaugustin
- Needs documentation unset
- Needs tests unset
- Owner changed from nobody to aaugustin
- Patch needs improvement unset
- Triage Stage changed from Unreviewed to Accepted
comment:3 Changed 2 years ago by aaugustin
- Resolution set to fixed
- Status changed from new to closed