MySQL datetime precision and Django RawQuerySet

I ran into an interesting bug with a Django-based API where database save operations that were committed during request processing were not being reflected in model RawQuerySets that were executed shortly after.

My first suspicion was some kind of transaction-related issue, but it turned out to be a datetime precision problem. Specifically, the datetime column that Django had created for the model field I was saving to in my database was set to precision 0, meaning the highest level of precision it was tracking was seconds. Yet the datetime values I was injecting into the manager’s raw() method, directly from the datetime python library, were precision 6 (microseconds).

This meant that when I dynamically built a query string to filter records greater than or equal to the date I had just set on my new object, that new object wasn’t even returning in the set.

It was because datetimes such as 2016–02–11 19:27:33.950000 were being injected into the query. Since the value MySQL was saving to the database, 2016–02–11 19:27:33, is NOT greater than or equal to 2016–02–11 19:27:33.950000, that record was excluded.

Here’s a simplified example to illustrate the issue further:

from datetime import datetime
from myapp.models import SimpleDatetime

# get now
now = datetime.now()

# create new object
simple_datetime = SimpleDatetime() # model with a single DateTimeField
simple_datetime.date = now
simple_datetime.save()
sd_id = simple_datetime.id

# go back to the DB for that datetime
sql = ‘SELECT * FROM simple_datetime where date >= “{}” and id = {}’.format(now, sd_id)
sd = SimpleDatetime.objects.raw(sql)

>>> print len(list(sd))
0

>>> print sql.raw_query
SELECT * FROM simple_datetime where date >= "2016-02-11 19:27:33.950000" and id = 105

The solution was to just truncate the datetime to whatever precision the database stores it before making the query.

This issue makes doing raw queries on dates somewhat risky unless you always know the precision of your backend at runtime. It would be nice if MySQL didn’t allow for a higher level of precision in queries than the columns support, although it’s possible that’s either configurable, or by design for a good reason.

Of course, one of the main advantages of Django is avoiding directly interfacing with the database. It’s clear that the Django developers don’t like people using raw queries, and this issue is one more reason to add to the list of reasons why.

For me, it’s just one more reason to tread carefully when intermixing ORMs with SQL.