QuerySet filtering weirdness for dates with 3-digit years

99 views
Skip to first unread message

Evan Carmi

unread,
Apr 14, 2012, 3:25:22 PM4/14/12
to django-d...@googlegroups.com
Hi all,

I'm leading a small group of CS majors in a software development forum focused on learning (and hopefully contributing to) Django at Wesleyan University.

While getting started with the Django Tutorial we noticed some odd behavior that seems to be a bug. If the case, we'd love to be the ones to help out and create a patch.

The issue is the following: Filtering using the QuerySet API seems to build incorrect SQL statements for early dates such as year 1.

An example from the Django Tutorial:

from polls.models import Polls
import datetime
>>> q = Poll(question="What's up?", pub_date=datetime.datetime.now())
>>> p = Poll(question="Will this work?", pub_date=datetime.datetime(1,1,1,1,1))

>>> p.save()
>>> q.save()

>>> Poll.objects.filter(pub_date__year=2012) # We should get the poll q (and we do)
[<Poll: What's up>]

>>> Poll.objects.filter(pub_date__year=1) #We should get the poll p (but we don't)
[]

This seems to be the case for all dates with a non 4 digit year (999, also has some problem).

The SQL queries that are being generated are (for sqlite):

{'sql': u'INSERT INTO "polls_poll" ("question", "pub_date") VALUES (Year is 999, 0999-09-09 00:00:00)',
 'time': '0.043'},
{'sql': u'SELECT "polls_poll"."id", "polls_poll"."question", "polls_poll"."pub_date" FROM "polls_poll" WHERE "polls_poll"."pub_date" BETWEEN 999-01-01 and 999-12-31 23:59:59.999999 LIMIT 21',
 'time': '0.000'}]


So it seems like there is a normalization process of converting all years into a 4 digit integer when creating objects, but when searching or filtering for objects the years the SQL isn't correct. I'd think the correct SQL statement should simply have the year dates have 4 digits like the SQL statement which works:

sqlite> SELECT "polls_poll"."id", "polls_poll"."question", "polls_poll"."pub_date" FROM "polls_poll" WHERE "polls_poll"."pub_date" BETWEEN "0999-01-01" and "0999-12-31 23:59:59.999999";
5|Year is 999|0999-09-09 00:00:00


If this is, in fact, a bug and not intentional we'd love to be the ones to create tests and a patch.

Cheers,
Evan

Andy McKay

unread,
Apr 14, 2012, 6:28:33 PM4/14/12
to django-d...@googlegroups.com
From my brief reading of the some docs (eg postgresql) it looks like
dates with less than 4 years do need to be prefixed with a zero, in
the standard date format (some databases let you set different
formats). But since python doesn't output a 0, I can see why its going
wrong:

>>> datetime(999,1,1,1).year
999

Interestingly sqlite uses standard strftime
http://www.sqlite.org/lang_datefunc.html:

%Y year: 0000-9999

I thought the answer might be to use strftime but:

>>> datetime(999,1,1,1).strftime('%Y')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
ValueError: year=999 is before 1900; the datetime strftime() methods
require year >= 1900

Which is this bug: http://bugs.python.org/issue1777412

This isn't a problem that I can imagine many people have run into, so
sounds like a bug needing a patch. Good luck!

Aymeric Augustin

unread,
Apr 15, 2012, 6:14:02 AM4/15/12
to django-d...@googlegroups.com
On 14 avr. 2012, at 21:25, Evan Carmi wrote:
>>> Poll.objects.filter(pub_date__year=1) #We should get the poll p (but we don't)
[]

The docs [1] say that the __year lookup "takes a four digit year". I'm not sure what this means exactly for years < 1000!

It looks like it's possible to improve either the code or the docs, so it's worth filing a ticket in Trac.

The SQL queries that are being generated are (for sqlite):
{'sql': u'INSERT INTO "polls_poll" ("question", "pub_date") VALUES (Year is 999, 0999-09-09 00:00:00)',
 'time': '0.043'},
{'sql': u'SELECT "polls_poll"."id", "polls_poll"."question", "polls_poll"."pub_date" FROM "polls_poll" WHERE "polls_poll"."pub_date" BETWEEN 999-01-01 and 999-12-31 23:59:59.999999 LIMIT 21',
 'time': '0.000'}]

Note that these aren't the actual SQL queries [2]. Stiil, they likely show the cause of the problem.

Best regards,

-- 
Aymeric.


Aymeric Augustin

unread,
Apr 15, 2012, 6:14:22 AM4/15/12
to django-d...@googlegroups.com
On 15 avr. 2012, at 00:28, Andy McKay wrote:
> Which is this bug: http://bugs.python.org/issue1777412

FYI Django uses the django.utils.datetime_safe module to work around this problem when necessary.

--
Aymeric.

Anssi Kääriäinen

unread,
Apr 16, 2012, 7:49:07 AM4/16/12
to Django developers
On Apr 15, 1:14 pm, Aymeric Augustin
<aymeric.augus...@polytechnique.org> wrote:
> On 14 avr. 2012, at 21:25, Evan Carmi wrote:
>
> > >>> Poll.objects.filter(pub_date__year=1) #We should get the poll p (but we don't)
> > []
>
> The docs [1] say that the __year lookup "takes a four digit year". I'm not sure what this means exactly for years < 1000!
>
> It looks like it's possible to improve either the code or the docs, so it's worth filing a ticket in Trac.

I guess we can't fix the code in a way that the meaning of
pub_date__year=1 changes. Would it be possible to change the code so
that '0001' is treated as year 1, and the integer 1 is treated as it
is now? Or maybe allow datetime objects as arguments to the year
lookup, then datetime(year=1) could be treated differently from
integer 1.

- Anssi

Evan Carmi

unread,
Apr 17, 2012, 9:38:11 PM4/17/12
to django-d...@googlegroups.com
On Sun, Apr 15, 2012 at 6:14 AM, Aymeric Augustin <aymeric....@polytechnique.org> wrote:
On 14 avr. 2012, at 21:25, Evan Carmi wrote:
>>> Poll.objects.filter(pub_date__year=1) #We should get the poll p (but we don't)
[]

The docs [1] say that the __year lookup "takes a four digit year". I'm not sure what this means exactly for years < 1000!

It looks like it's possible to improve either the code or the docs, so it's worth filing a ticket in Trac.

Okay, will file a ticket in Trac and pursue a docs clarification or code patch from there.

Thanks,
Evan

Evan Carmi

unread,
Apr 19, 2012, 9:44:33 PM4/19/12
to django-d...@googlegroups.com
We created a ticket in Trac: https://code.djangoproject.com/ticket/18176

I guess the decision regarding whether this is a docs clarification or a code patch will be made there. Although if someone would like to help expedite that process we would love to do the work for the fix in either direction.

Cheers,
Evan
Reply all
Reply to author
Forward
0 new messages