在django过滤器中使用dateadd [英] Using dateadd in django filter
问题描述
我有一个模型,按开始日期和持续时间(天)定义订阅期限:
I have a model that defines subscription periods by start date and duration (in days):
class SubscriptionProduct(models.Model):
start_date = models.DateField()
duration = models.IntegerField()
我需要能够筛选当前活动的订阅,例如 start_date<现在< start_date + duration
I need to be able to filter subscriptions that are currently active, e.g. start_date < now < start_date+duration
我找不到django的方法。我可以使用使用Postgres'DATEADD等同于INTERVAL的原始SQL语句,但我更喜欢使用内置的和非特定的数据库。
I can't find the django way to do it. I can use raw SQL statements that use postgres' DATEADD equivalent of INTERVAL but i'd prefer to use something builtin and non db specific.
我认为我很想看用于dateadd注释方法。如下:
I assume ideally i'm looking for a dateadd annotation method. Something like:
SubscriptionProduct.objects.annotate(end_date=DateAdd('start_date','duration').filter(start_date__lt=datetime.now, end_date__gte=datetime.now)
推荐答案
最终编写了一个自定义的Func表达式,它精确地执行了我正在寻找的内容。
这是非常Postgresql特定的,有点黑客,但它可以工作,即使在比上面所示的更复杂的查询中使用。
I ended up writing a custom Func expression that does exacly what I was looking for. This is very Postgresql specific and a bit hacky but it works, even when used in more complex queries than the one illustrated above.
class DateAdd(Func):
"""
Custom Func expression to add date and int fields as day addition
Usage: SubscriptionProduct.objects.annotate(end_date=DateAdd('start_date','duration')).filter(end_date__gt=datetime.now)
"""
arg_joiner = " + CAST("
template = "%(expressions)s || ' days' as INTERVAL)"
output_field = DateTimeField()
请注意,我不得不按照f执行arg_joiner技巧或两个字段名称在subselect表达式
Note that I had to do the arg_joiner trick in order for both field names to be resolved properly when used in subselect expressions
这篇关于在django过滤器中使用dateadd的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!