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?
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')
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).
__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...
order_by over the generated QuerySet by the above mentioned filter. I haven't used Django in years.
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))
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.
date object: >>> from datetime import date >>> startdate = date.today()
__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 callYou 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.
Example.objects.filter(created__range=[date(2014, 1, 1), date(2014, 2, 1)]) would not include objects created on date(2014, 2, 1), as @cademan 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.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])
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
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
Is simple,
YourModel.objects.filter(YOUR_DATE_FIELD__date=timezone.now())
Works for me
