'week' queryset returns no objects

[Andrew Williams]

Using the 'week' queryset modifier results in no objects being returned, despite there being objects in the database. Here's a simplified version of the model being used:

import datetime
import pytz

def timestamp():
    return pytz.utc.localize(datetime.datetime.utcnow())

class ReportConfig(models.Model):
    last_modified = models.DateTimeField(default=timestamp)

From the shell:

In [54]: ReportConfig.objects.count()
Out[54]: 853

In [55]: ReportConfig.objects.filter(last_modified__week__gte=0,last_modified__week__lte=53).count()
Out[55]: 0

In [56]: ReportConfig.objects.filter(last_modified__week=34).count()
Out[56]: 0

In [57]: ReportConfig.objects.last().last_modified.isocalendar()[1]
Out[57]: 34

I'm using Django 2.2.4 and MySQL Server version: 5.7.25-log

[Nasir Hussain]

Looks like its fixed in master branch by this ​commit which is part of #28373. We might need to take that fix to 2.2.5 too.

[Mariusz Felisiak]

I cannot reproduce this issue on Django == 2.2.4 or master. Can you provide a sample project?

[Andrew Williams]

Looking more into this, it turns out that this is a side-effect of mysql not having the timezone definitions in the database (See ​https://docs.djangoproject.com/en/dev/ref/models/querysets/#database-time-zone-definitions)

Behind the scenes, the first query above gets translated into:

 SELECT COUNT(*) AS `__count` FROM `mymodel_reportconfig` WHERE (WEEK(CONVERT_TZ(`mymodel_reportconfig`.`last_modified`, 'UTC', 'UTC'), 3) >= 0 AND WEEK(CONVERT_TZ(`mymodel_reportconfig`.`last_modified`, 'UTC', 'UTC'), 3) <= 53)

The CONVERT_TZ calls return NULL due to the timezone definitions not being loaded:

mysql> SELECT
    ->   CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central') AS time1,
    ->   CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central') AS time2;
+-------+-------+
| time1 | time2 |
+-------+-------+
| NULL  | NULL  |
+-------+-------+
1 row in set (0.00 sec)