How do I filter query objects by date range in Django?

Question

I've got a field in one model like:

class Sample(models.Model):
    date = fields.DateField(auto_now=False)

Now, I need to filter the objects by a date range.

How do I filter all the objects that have a date between 1-Jan-2011 and 31-Jan-2011?

Acis

Answer 1

Use

Sample.objects.filter(date__range=["2011-01-01", "2011-01-31"])

Or if you are just trying to filter month wise:

Sample.objects.filter(date__year='2011', 
                      date__month='01')

Edit

As Bernhard Vallant said, if you want a queryset which excludes the specified range ends you should consider his solution, which utilizes gt/lt (greater-than/less-than).

Crookes

What's date1's datatype? I've got datetime object now. – Acis

@dcordjer: Additinally should be said that __range includes the borders (like sql's BETWEEN), if you don't want the borders included you would have to go with my gt/lt solution... – Normative

Is this inherently sorted in any order? If so, which order? Thanks. – Dingess

@RichardDunn The ordering will be based on your model's default ordering, or if you use order_by over the generated QuerySet by the above mentioned filter. I haven't used Django in years. – Crookes

for date__range you need to put 01 of the next month. Here is a link to the documentaion that exmaplins that it translates to 00:00:00.0000 of the dates, hence the last day in your range is not included. docs.djangoproject.com/en/1.10/ref/models/querysets/#range in this case i use: date__range=["%s-%s-1"%(year,month),"%s-%s-1"%(year,int(month)+1)] – Ciera

Is there a default range for this filter? – Rosalindrosalinda

no default range. Also the limitation about it having to be strings is no longer true docs.djangoproject.com/en/3.0/ref/models/querysets/#range works with strings and datetime objects. – Rhea

Come on! It is just this simple? Through SO has many answers and this one is the best I have seen. Thank you. – Howlend

Answer 2

You can use django's filter with datetime.date objects:

import datetime
samples = Sample.objects.filter(sampledate__gte=datetime.date(2011, 1, 1),
                                sampledate__lte=datetime.date(2011, 1, 31))

Normative

Answer 3

When doing django ranges with a filter make sure you know the difference between using a date object vs a datetime object. __range is inclusive on dates but if you use a datetime object for the end date it will not include the entries for that day if the time is not set.

from datetime import date, timedelta

startdate = date.today()
enddate = startdate + timedelta(days=6)
Sample.objects.filter(date__range=[startdate, enddate])

returns all entries from startdate to enddate including entries on those dates. Bad example since this is returning entries a week into the future, but you get the drift.

from datetime import datetime, timedelta

startdate = datetime.today()
enddate = startdate + timedelta(days=6)
Sample.objects.filter(date__range=[startdate, enddate])

will be missing 24 hours worth of entries depending on what the time for the date fields is set to.

Triphylite

I think it is important to note how to import a date object: >>> from datetime import date >>> startdate = date.today() – Millham

If using a DateTime Model field for the query for __range you can also define the range to be based on dates without time like so: date_entered__date__range=[date1, date2]. Note I called the DB field date_entered to not confuse the __date call – Peaked

Answer 4

You can get around the "impedance mismatch" caused by the lack of precision in the DateTimeField/date object comparison -- that can occur if using range -- by using a datetime.timedelta to add a day to last date in the range. This works like:

start = date(2012, 12, 11)
end = date(2012, 12, 18)
new_end = end + datetime.timedelta(days=1)

ExampleModel.objects.filter(some_datetime_field__range=[start, new_end])

As discussed previously, without doing something like this, records are ignored on the last day.

Edited to avoid the use of datetime.combine -- seems more logical to stick with date instances when comparing against a DateTimeField, instead of messing about with throwaway (and confusing) datetime objects. See further explanation in comments below.

Cembalo

There's an awesome Delorean library that simplifies this with a truncation method: delorean.readthedocs.org/en/latest/quickstart.html#truncation – Cembalo

@tojjer: looks promising, how do we use the truncate method here though? – Riva

@eugene: I explored this again just now, after all those months, and you're right in that it doesn't really help in this situation after all. The only way around it that I can think of is as suggested in my original response, which is to supply the extra 'padding' for comparison against a datetime model field when you're filtering against a date instance. This can be done via the datetime.combine method as above, but I've found that it can be a bit simpler to merely accommodate the discrepancy by adding a timedelta(days=1) to either the start/end date in the range -- depending on the problem. – Cembalo

So Example.objects.filter(created__range=[date(2014, 1, 1), date(2014, 2, 1)]) would not include objects created on date(2014, 2, 1), as @Triphylite explained helpfully. But if you incremented the end date by adding one day, you'd get a queryset covering those missing objects (and conveniently omitting objects created on date(2014, 2, 2) because of the same quirk). The annoying thing here is that a 'manual' range specified with created__gte ... created__lte=date(2014, 2, 1) doesn't work either, which is definitely counter-intuitive IMHO. – Cembalo

@tojjer: datetime_field__range = [delorean.parse('2014-01-01').date, delorean.parse('2014-02-01').date] works for me – Riva

Are you sure it works @eugene? I don't see how it can, as you're still comparing a datetime.date instance with a DateTimeField -- the problem as described occurs because of the lack of precision; there's no time data to compare with. Let me know if by "work" you mean that it actually returns objects where datetime_field refers to 2014-02-01! :) BTW, delorean.parse('2014-01-01').date == dateutil.parser.parse('2014-01-01').date() in this case; Delorean is great but unnecessary if this is all we're doing. A nice library though! – Cembalo

Also note that Django can handle the date parsing for you in a queryset. It's sometimes useful to be able to directly pass in date strings as follows: Example.objects.filter(datetime_field__range=['2014-01-01', '2014-02-01']). I haven't been able to get it to include objects where the datetime_field refers to the last day though, to reiterate -- without hacking by adding one day to the end date. – Cembalo

This answer is almost the correct way to handle this, but it's incorrect for two reasons. 1. This answer doesn't handle issues with other timezones. You need the time at midnight in the customer's timezone, not in UTC or the server's timezone. 2. The upper bound here on midnight is inclusive. It needs to be exclusive. – Balcom

Answer 5

you can use "__range" for example :

from datetime import datetime
start_date=datetime(2009, 12, 30)
end_date=datetime(2020,12,30)
Sample.objects.filter(date__range=[start_date,end_date])

Ashraf

Answer 6

To make it more flexible, you can design a FilterBackend as below:

class AnalyticsFilterBackend(generic_filters.BaseFilterBackend):
    def filter_queryset(self, request, queryset, view):
        predicate = request.query_params # or request.data for POST

        if predicate.get('from_date', None) is not None and predicate.get('to_date', None) is not None:
            queryset = queryset.filter(your_date__range=(predicate['from_date'], predicate['to_date']))

        if predicate.get('from_date', None) is not None and predicate.get('to_date', None) is None:
            queryset = queryset.filter(your_date__gte=predicate['from_date'])

        if predicate.get('to_date', None) is not None and predicate.get('from_date', None) is None:
            queryset = queryset.filter(your_date__lte=predicate['to_date'])
        return queryset

Blackcock

Answer 7

Is simple,

YourModel.objects.filter(YOUR_DATE_FIELD__date=timezone.now())

Works for me

Mccallion

This worked for me as well, for the noobs for clarity: (date__date=...) means ({whateverColumnTheDateIsCalled}__date) – Medley

OP asked for a range however – Kevyn

Answer 8

Model

date = models.DateField()

View

def get_queryset(self):  

    fromDate = self.request.query_params.get('fromDate',None)
    toDate = self.request.query_params.get('toDate',None)
    response  = yourModel.objects.filter(date__gte=fromDate,date__lte=toDate)
    return response

Alceste