How can I filter a date of a DateTimeField in Django?

Question

I am trying to filter a DateTimeField comparing with a date. I mean:

MyObject.objects.filter(datetime_attr=datetime.date(2009,8,22))

I get an empty queryset list as an answer because (I think) I am not considering time, but I want "any time".

Is there an easy way in Django for doing this?

I have the time in the datetime setted, it is not 00:00.

Solution 1

Such lookups are implemented in django.views.generic.date_based as follows:

{'date_time_field__range': (datetime.datetime.combine(date, datetime.time.min),
                            datetime.datetime.combine(date, datetime.time.max))} 

Because it is quite verbose there are plans to improve the syntax using __date operator. Check "#9596 Comparing a DateTimeField to a date is too hard" for more details.

Solution 2

YourModel.objects.filter(datetime_published__year='2008', 
                         datetime_published__month='03', 
                         datetime_published__day='27')

// edit after comments

YourModel.objects.filter(datetime_published=datetime(2008, 03, 27))

doest not work because it creates a datetime object with time values set to 0, so the time in database doesn't match.

Solution 3

Here are the results I got with ipython's timeit function:

from datetime import date
today = date.today()

timeit[Model.objects.filter(date_created__year=today.year, date_created__month=today.month, date_created__day=today.day)]
1000 loops, best of 3: 652 us per loop

timeit[Model.objects.filter(date_created__gte=today)]
1000 loops, best of 3: 631 us per loop

timeit[Model.objects.filter(date_created__startswith=today)]
1000 loops, best of 3: 541 us per loop

timeit[Model.objects.filter(date_created__contains=today)]
1000 loops, best of 3: 536 us per loop

contains seems to be faster.

Solution 4

Now Django has __date queryset filter to query datetime objects against dates in development version. Thus, it will be available in 1.9 soon.

Solution 5

Mymodel.objects.filter(date_time_field__contains=datetime.date(1986, 7, 28))

the above is what I've used. Not only does it work, it also has some inherent logical backing.

View more solutions

Comments

[Haskell-newb]

This is one of annoyances of Django. Considering this is a simple and common use case, there's no simple way to achieve this.

[MackM]

Related: stackoverflow.com/a/22085678/2859614

[Xidobix]

thx for the answer! the first alternative doesn't work with datetimefields. The second alternative works ;). If someone knows another method please answer

[zalew]

docs.python.org/library/datetime.html#datetime-objects using datetime() from datetime module hrs,mins,secs is optional. the second is from a working project with vars replaced, you can look in the docs it's correct

[Xidobix]

in the django documentation it works because the datetimefiled has time 00:00

[Xidobix]

i know it is optional, the problem is that my datetimefield has the time setted, it is not 00:00

[zalew]

"the first alternative doesn't work with datetimefields." it'd be quite surprising, as datetime.datetime() returns a datetime object djangoproject.com/documentation/0.96/models/basic check the model definition and examples: pub_date = models.DateTimeField() pub_date=datetime(2005, 7, 30)

[zalew]

"i know it is optional, the problem is that my datetimefield has the time setted, it is not 00:00" Oh, now i get it. Yes, with no time arguments it sets to 00, so it does not return :)

[yilmazhuseyin]

looks like this one turns date object to string and do a string comparison of dates therefore forces db to do a full table scan. for big tables this one kill your performance

[Dingo]

Using with range: Q(created__gte=datetime.combine(created_value, time.min))

[bbengfort]

Ok, so this does appear to be the same answer as mhost and kettlehell above, but with more description of what is happening in the backend. At least you have a reason to use contains or startswith along with the date() attribute of the datetime!

[Houman]

This solution seems to be the most recent. I am surprised it got 4 upvotes, because when I try the contains solution, I get the error message: Unable to get repr for <class 'django.db.models.query.QuerySet'>

[Moreno]

I recheck and update the results today and I don't think your error it's caused by the __contains filter. But if you're running into issues you should try the django docs example which is using __gte.

[RobotHumans]

The __contains method works fine for me. I think this is probably the best answer since it provides performance comparisons. I've voted more than one, but I'm surprised it doesn't have more upvotes.

[Kin]

Much better than all of the other answers here, thanks!

[amjoconn]

Looks like it will land in Django 1.9: github.com/django/django/commit/…

[Binoj D]

is this MySQL specific? wondering about the class name choice.

[Dan Gayle]

@BinojDavid Yeah, it is backend dependent

[Jim Paul]

I'm using django 1.9 and python 2.7. This solution did not work for me. I can filter on the year, but not the month or day. Thus YourModel.objects.filter(datetime_published__year='2008') is the only thing that worked. Bug in django 1.9?

[gdvalderrama]

This only works if you're looking for the exact date, you can't use __lte for example.

[gdvalderrama]

Yup, it was added in 1.9 docs.djangoproject.com/en/1.9/ref/models/querysets/#date

[serfer2]

Best answer, valid for newer Django versions

[Non]

New in Django 1.9: Entry.objects.filter(pub_date__date=datetime.date(2005, 1, 1))

[michal-michalak]

I tested it using Django 1.8 and PostgreSQL 9.6.6 and it works perfectly. Actually using PostgreSQL you can also use this syntax: return '{}::date'.format(lhs), params

[wrivas]

Simple is better than complex =)

[AbdurRehman Khan]

This does not work for me, no idea why :( I'm on django 1.11 My exact exception is: NotImplementedError: subclasses of basedatabaseoperations may require a datetime_cast_date() method

[Prajwal Kulkarni]

I read several answers stating about the '__date' , but I found all of them quite sophisticated, until I read your answer. It's simple and straight to the point.

[Sara Sherif]

@AbdurRehmanKhan yes I am facing the same issue currently, using mongodb did you found any useful solution?