Django，按指定的月份和年份在日期范围内进行筛选

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

这是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.

如果指定的月份和日期在date_from和date_to之间，我想过滤一下Destination_Deals。

Example 1

示例1

Month: September (09)
Year: 2012

月份:09年9月2012年

Wanted departure dates result:
ID 3 : It is the only data range that touch 09/2012

想要的出发日期结果:ID 3:这是唯一一个触及09/2012的数据范围

Example 2

示例2

Month: February (02)
Year: 2013

月份:02年2月:2013年

Wanted departure dates result:
ID 1 : 02/2012 is before 03/2012

想要出发日期的结果:ID 1: 02/2012是在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.

所以，实际上这一天并不重要。如果月和年在date_from和date_to之间，即使它是一天，它也必须是过滤器。

I think I must use something like this but I am not sure how to do it.

我想我必须用这样的东西，但我不知道该怎么做。

Thanks in advance! Miguel

提前谢谢!米盖尔

---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.

---编辑---这是对Aamir Adnan的答案的测试，但它并没有像我预期的那样发挥作用，因为ID 1也必须被归还，因为它是从2012年11月到2013年3月，所以2013年1月介于两者之间。

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>]

3 个解决方案

#1

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:

使用.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:

然后%(月)的>=月(date_from)条件是错误的，因为第1个月是date_from的第11个月，但年份是不同的，所以MySQL如果条件是需要的:

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])

#2

Solution using python code only. Main idea is to construct date_from and date_to with python. Then these dates can be used in filter with __lte and __gte:

只使用python代码的解决方案。主要思想是用python构造date_from和date_to。然后这些日期可以用在__lte和__gte的过滤器中:

import calendar
from datetime import datetime
from django.db.models import Q

def in_month_year(month, year):
    d_fmt = "{0:>02}.{1:>02}.{2}"
    date_from = datetime.strptime(
        d_fmt.format(1, month, year), '%d.%m.%Y').date()
    last_day_of_month = calendar.monthrange(year, month)[1]
    date_to = datetime.strptime(
        d_fmt.format(last_day_of_month, month, year), '%d.%m.%Y').date()
    return Departure_Date.objects.filter(
        Q(date_from__gte=date_from, date_from__lte=date_to)
         |
        Q(date_from__lt=date_from, date_to__gte=date_from))

Now this will work:

现在这将工作:

>>> 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>]


>>> in_month_year(month=1, year=2013)
[<Departure_Date: id: 1 - from: 2012-11-01 - to: 2013-03-17>,
<Departure_Date: id: 5 - from: 2013-01-04 - to: 2013-01-11>]

#3

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:

您可以通过使用DateTimeField/date对象比较(如果使用范围的话)，避免由于缺乏精度而导致的“阻抗不匹配”。增加一天到最后一天的时间间隔。这就像:

import datetime

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])

#1