Problem with filtering on DateTimeField

[Peter Bengtsson]

I've got something like this:

class Class(models.Model):
...
date = models.DateTimeField()

I'm trying to filter and get all objects on a particular day:

>>> Class.objects.all()[0].date
datetime.datetime(2008, 9, 4, 15, 10, 23, 359032)
>>> Class.objects.filter(date=datetime(2008,9,4)).count()
0

This is unexpected. Clearly there is one object in there on the same
day, but obviously at a different hour.
I've also tried date__exact= and date__startswith= but no luck with
either. I know it's possible to do things like date__year=,
date__month=, date__day= but that feels a bit excessive.

At the moment I've solved my problem by doing a __range but I really
want to know why it doesn't work.

Peter

[akaihola]

First, look at the behavior of Python's datetime module:

>>> datetime(2008,9,4)
datetime.datetime(2008, 9, 4, 0, 0)

Omitting the time part is just a shorthand for midnight.

I don't think that comparing microsecond-accurate timestamps to date
values would make sense at the database level anyway.

I've used __range as well in the past successfully with some helper
functions to auto-generate the boundaries from a date object.

When in doubt, dumping the queries Django generates will give you some
insight (assuming you're SQL literate):

>>> Class.objects.filter(date=datetime(2008,9,4)).query.as_sql()

[Karen Tracey]

It doesn't work because a datetime has more pieces than year, month, day, and if you do an exact lookup with two datetimes that differ in the parts you don't care about, they aren't going to come out as matching simply because you didn't specify values for the pieces you are not interested in.  Those values are going to get set to something, I'd guess zero.  If that's not what the values are for the "matching" entries in the DB, than an exact lookup won't find them.

See:

(InteractiveConsole)
>>> from dttest.models import Twd
>>> import datetime
>>> twd = Twd(date=datetime.datetime.today())
>>> twd.save()
>>> twd = Twd.objects.all()[0]
>>> twd
<Twd: 2008-09-05 19:40:44>
>>> x = Twd.objects.filter(date=datetime.datetime(2008,9,5))[0]
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/kmt/tmp/django/trunk/django/db/models/query.py", line 232, in __getitem__
    return list(qs)[0]
IndexError: list index out of range
>>> from django.db import connection
>>> connection.queries[-1]
{'time': '0.001', 'sql': u'SELECT `dttest_twd`.`id`, `dttest_twd`.`date` FROM `dttest_twd` WHERE `dttest_twd`.`date` = 2008-09-05 00:00:00  LIMIT 1'}

The matching-day object wasn't found because it doesn't match on the hh:mm:ss field.  startswith can be made to work, but you have to make sure to pass in just a date, not a full datetime for the comparison.  If you pass in a full datetime, you'll still be trying to match on 0's for the time.  In fact on MySQL you'll get an error:

>>> x = Twd.objects.filter(date__startswith=datetime.datetime(2008,9,5))[0]
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/kmt/tmp/django/trunk/django/db/models/query.py", line 232, in __getitem__
    return list(qs)[0]
  File "/home/kmt/tmp/django/trunk/django/db/models/query.py", line 156, in __len__
    self._result_cache.extend(list(self._iter))
  File "/home/kmt/tmp/django/trunk/django/db/models/query.py", line 269, in iterator
    for row in self.query.results_iter():
  File "/home/kmt/tmp/django/trunk/django/db/models/sql/query.py", line 206, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/home/kmt/tmp/django/trunk/django/db/models/sql/query.py", line 1700, in execute_sql
    cursor.execute(sql, params)
  File "/home/kmt/tmp/django/trunk/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
  File "/home/kmt/tmp/django/trunk/django/db/backends/mysql/base.py", line 83, in execute
    return self.cursor.execute(query, args)
  File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 168, in execute
    if not self._defer_warnings: self._warning_check()
  File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 82, in _warning_check
    warn(w[-1], self.Warning, 3)
  File "warnings.py", line 62, in warn
    globals)
  File "warnings.py", line 102, in warn_explicit
    raise message
Warning: Incorrect datetime value: '2008-09-05 00:00:00%' for column 'date' at row 1
>>> connection.queries[-1]
{'time': '0.002', 'sql': u'SELECT `dttest_twd`.`id`, `dttest_twd`.`date` FROM `dttest_twd` WHERE `dttest_twd`.`date` LIKE BINARY 2008-09-05 00:00:00%  LIMIT 1'}

because apparently MySQL doesn't like the % in that query to be placed beyond (or within) the seconds part of the datetime.  But if you pass in just a date:

>>> x = Twd.objects.filter(date__startswith=datetime.date(2008,9,5))[0]
>>> x
<Twd: 2008-09-05 19:40:44>
>>> connection.queries[-1]
{'time': '0.001', 'sql': u'SELECT `dttest_twd`.`id`, `dttest_twd`.`date` FROM `dttest_twd` WHERE `dttest_twd`.`date` LIKE BINARY 2008-09-05%  LIMIT 1'}
>>>

you get the result you are looking for.   (These startswith results may be database-dependent, though, I haven't tried on anything other than MySQL.)

Karen

[Justin Myers]

I use a couple of filters, which I think I first found in the code for
generic views:

from datetime import date, time, datetime
Class.objects.filter(date__gte=datetime.combine(date(int(year),int(month),int(day)),time.min)).filter(date__lte=datetime.combine(date(int(year),int(month),int(day)),time.max)).count()

Of course, I'm sure there's a cleaner way to write that--but it does
the job.

Hope that helps!
-Justin