Django, filter by specified month and year in date range
django, python
Solution
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])
Problem
I have the following models ``` class Destination_Deal(models.Model): name = models.CharField(_("Nombre"),max_length=200) class Departure_Date(models.Model): date_from= models.DateField(_('Desde')) date_to= models.DateField(_('Hasta')) destination_deal = models.ForeignKey(Destination_Deal,verbose_name = _("Oferta de Destino")) ``` This is the actual data in the table departure_date ``` id date_from date_to destination_deal_id 1 2012-11-01 2013-03-17 1 2 2012-11-01 2012-12-16 2 3 2012-09-16 2012-10-31 3 4 2012-11-01 2012-12-16 3 5 2013-01-04 2013-01-11 4 ``` I would like to filter the Destination_Deals if a specified month&year is between date_from and date_to. Example 1 Month: September (09) Year: 2012 Wanted departure dates result: ID 3 : It is the only data range that touch 09/2012 Example 2 Month: February (02) Year: 2013 Wanted departure dates result: ID 1 : 02/2012 is before 03/2012 So, the day actually is does not matter. If the month&year is between date_from and date_to, even if it is by one day it must be filter. I think I must use something like this but I am not sure how to do it. ---Edit--- This is the test for the answer from Aamir Adnan but it is not working as I expected as ID 1 must be also returned because it goes from November 2012 to March 2013, so January 2013 is between. ``` Departure_Date.objects.all() [<Departure_Date: id: 1 - from: 2012-11-01 - to: 2013-03-17>, <Departure_Date: id: 2 - from: 2012-11-01 - to: 2012-12-16>, <Departure_Date: id: 3 - from: 2012-09-16 - to: 2012-10-31>, <Departure_Date: id: 4 - from: 2012-11-01 - to: 2012-12-16>, <Departure_Date: id: 5 - from: 2013-01-04 - to: 2013-01-11>] month:1 year:2013 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]) [<Departure_Date: id: 5 - from: 2013-01-04 - to: 2013-01-11>] ```