There was some short discussion in ticket #17728 about doing timezone
conversions for aware datetimes using cursor adapters. The design
decision was that it should not be done because conversions are
already done by field classes, and thus the ORM should not pass aware
datetimes into cursors. I disagree because raw SQL users will get
wrong results. Field conversions do not help when using raw SQL.
The basic problem is this:
dt = datetime.datetime.now().replace(tzinfo='Europe/Helsinki')
cursor.execute("select * from sometable where last_edited < %s",
(dt,))
The query will compare the dt's value in Europe/Helsinki against
last_edited in UTC. Thus there will be two hours mistake in the value
of dt in the query. This does not happen on PostgreSQL which handles
datetimes correctly, but does happen on at least SQLite and MySQL.
My view is that the dt should be converted automatically to UTC, thus
ensuring consistent and correct results across database backends.
Django is emulating time zone support for SQLite and MySQL and it is
Django's responsibility to do that emulation for raw queries, too.
The ORM uses conversions done by field classes. The conversions done
by Field classes are for decimal, time, date and datetime. Of these
only timezone aware datetimes work incorrectly if used in raw SQL.
Decimals would not work correctly under sqlite, but there is an
adaptor registered for Decimals. So, there is a precedence for adaptor
usage if they are required for correct behavior. There seems to be
some adaptor business going on for oracle backend, too. (It is pretty
complicated to check how different backends work. Mistakes possible
here.)
If there is a final decision on "no adapters" then at least provide
documentation on how to do correctly queries using aware datetimes.
For example, the above query would be:
cursor.execute("select * from sometable where last_edited < %s,
(connection.ops.value_to_db_datetime(dt),))
Additionally, raise an exception if an aware datetime is passed to the
cursor. That way it would be easier to spot bugs both in ORM and in
user queries.
I feel strongly that the decision about no adapters is wrong in this
case. Django is failing to emulate the "datetimes as UTC" solution for
SQLite and MySQL when doing raw queries (Oracle likely too). The
current behavior _will_ cause bugs in user code.
- Anssi
--
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en.
