Python Django，按日期范围内的指定月份和年份过滤
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14077799/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Django, filter by specified month and year in date range
提问by Miguel Febres
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
这是表department_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)
年份：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)
年份：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 thisbut 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>]
采纳答案by Aamir Adnan
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_frombut year is different so MySQL IFcondition is required here:
然后%(month)s >= MONTH(date_from)条件错误，因为第 1 个月 < 第 11 个月date_from但年份不同，因此IF此处需要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])
回答by stalk
Solution using python code only. Main idea is to construct date_from and date_to with python. Then these dates can be used in filterwith __lteand __gte:
仅使用 python 代码的解决方案。主要思想是用python构造date_from和date_to。然后这些日期可以filter与__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>]
回答by Alvaro Joao
You can get around the "impedance mismatch" caused by the lack of precision in the DateTimeField/dateobject comparison -- that can occur if using range-- by using a datetime.timedeltato add a day to last date in the range. This works like:
您可以通过使用datetime.timedelta将日期添加到范围中的最后一个日期来解决由于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])
