Django, filter by specified month and year in date range

by

Check the documentation

year = 2012
month = 09
Departure_Date.objects.filter(date_from__year__gte=year,
                              date_from__month__gte=month,
                              date_to__year__lte=year,
                              date_to__month__lte=month)

Alternative method using .extra:

where="%(year)s >= YEAR(date_from) AND %(month)s >= MONTH(date_from) \
        AND %(year)s <= YEAR(date_to) AND %(month)s <= MONTH(date_to)" % \
        {'year': year, 'month': month}
Departure_Date.objects.extra(where=[where])

There is a specific case where above query does not yield a desired result.

For example:

date_from='2012-11-01'
date_to='2013-03-17'
and input is
year=2013
month=1

Then %(month)s >= MONTH(date_from) condition is wrong because month 1 is < month 11 in date_from but year is different so MySQL IF condition is required here:

where="%(year)s >= YEAR(date_from) AND IF(%(year)s > YEAR(date_from), \
     IF(%(month)s > MONTH(date_from), %(month)s >= MONTH(date_from), %(month)s < MONTH(date_from)), \
     IF(%(month)s < MONTH(date_from), %(month)s < MONTH(date_from), %(month)s >= MONTH(date_from))) \
     AND %(year)s <= YEAR(date_to) \
     AND %(month)s <= MONTH(date_to)" % \
     {'year': year, 'month': month}
Departure_Date.objects.extra(where=[where])

Leave a Comment