How to filter gte,lte date on datetime field?

Milano picture Milano · Sep 13, 2017 · Viewed 10.5k times · Source

I'm trying to figure out how to filter QuerySet using date extracted from datetime.

I use Django-filter and I can't compose such lookup without iterating over QuerySet which is very uneffective.

I tried datetime__date__gte which doesn't work.

class MyReservationsFilter(FilterSet):
    datetime__lte = DateFilter(method='datetime_filter',name='lte')
    class Meta:
        model = Reservation
        fields = {'status': ['exact'],
                  # 'datetime': ['lte', 'gte'],
                  'destination_from': ['exact'],
                  'destination_to': ['exact'],}


    def datetime_filter(self, queryset, name, value):

        lookup = 'datetime__'+name
        return queryset.filter(**{lookup:value})

Do you know what to do?

I can't use datetime__lte/gte because:

I have a Reservation object r.

>> r.datetime
>> datetime.datetime(2017, 8, 31, 17, 55)
>> r.datetime.date()
>> datetime.date(2017, 8, 31)

So now I try to filter based on date:

Reservation.objects.filter(datetime__lte=datetime.date(2017, 8, 31),datetime__gte=datetime.date(2017, 8, 31))

>> []

It's because it doesn't look only on date but on time too. So I need to extract date to be able to filter properly.

Answer

Sherpa picture Sherpa · Sep 13, 2017

When you want to only compare the date portion of a datetime field, you need to use the date transform. eg,

Reservation.objects.filter(datetime__date__lte=date(2017, 1, 1))

Getting this to work with FilterSets doesn't require a custom method. Filter expressions are constructed by joining the field_name and lookup_expr arguments of the filter constructor. The field_name argument should correspond to the model field name, and the lookup_expr should account for the transform and lookup type.

class MyReservationsFilter(FilterSet):
    datetime__lte = DateFilter(field_name='datetime', lookup_expr='date__lte')

    class Meta:
        model = Reservation