Django cast DATE field to DATETIME unexpectedly

[Qian Xu]

I'm using django 1.8.4 writing a webapp. The backend uses MySQL 5.6 (MyISAM). Recently the number of table records reaches 1 million, it will take 1-1.5 seconds to query all distinct record dates. But using MySQL client, it takes less than 0.001 second.

Django Code

class Model1(models.Model):
    date = models.DateField(db_index=True)

# benchmark code
db_dates = Model1.objects.dates("date", kind="day")

I dumped the django queries.

{u'time': u'1.989', u'sql': u"SELECT DISTINCT CAST(DATE_FORMAT(`app1_model1`.`date`, '%Y-%m-%d 00:00:00') AS DATETIME) AS `datefield` FROM `app1_model1` WHERE `app1_model1`.`date` IS NOT NULL ORDER BY `datefield` ASC"}

As you can see, the query did possibly an unnecessary type cast. The performance impact is scaled to the amount of records. The table field is exactly DATE type. I don't know, if there is some reason for the type casting.

Currently, I use a workaround:

db_dates = Model1.objects.values_list('date', flat=True).distinct()

I posted a question at ​StackOverflow.