Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#30726 closed Bug (needsinfo)

'week' queryset returns no objects

Reported by: Andrew Williams Owned by: nobody
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Change History (4)

comment:1 by Nasir Hussain, 5 years ago

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.

comment:2 by Nasir Hussain, 5 years ago

Has patch: set
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug

comment:3 by Mariusz Felisiak, 5 years ago

Has patch: unset
Resolution: needsinfo
Status: newclosed
Triage Stage: AcceptedUnreviewed
Version: 2.2master

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

comment:4 by Andrew Williams, 5 years ago

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 'filter(last_modifiedweekgte=0,last_modifiedweeklte=53).count()' 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)
Version 0, edited 5 years ago by Andrew Williams (next)
Note: See TracTickets for help on using tickets.
Back to Top