Using dateadd in django filter

Question

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

I need to be able to filter subscriptions that are currently active, e.g. start_date < now < start_date+duration

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.

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)

Variant · Accepted Answer · 2015-11-30T12:25:08.547

9

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

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

edited Nov 30 '15 at 12:25

answered Nov 30 '15 at 12:15

Variant

17,279
4
40
65

1

For those looking to do it in MS SQL Server, one can just do: `function = "DATEADD"` and `template = '%(function)s(DAY, %(expressions)s)'` and use the number of days as first argument. – Eric Darchis Oct 29 '20 at 08:55
Docstring mentions `end_date__gt=datetime.now`, but it should be `end_date__gt=datetime.now()`. – Neamar Nov 23 '21 at 17:06

score 4 · Answer 2 · answered Jun 23 '21 at 19:17

here is the version for MySQL:

from django.db.models import Func, DateTimeField

class DateAdd(Func):
    function = 'DATE_ADD'
    template = "%(function)s(%(expressions)s, INTERVAL %(days)i DAY)"
    output_field = DateTimeField()

and then

queryset.annotate(days_added=DateAdd(f'date_field_name', days=1))

score 0 · Answer 3 · answered Oct 22 '22 at 10:00

In my way, this is work version:

class DateAdd(Func):
    function = 'DATE_ADD'                            # function name
    arg_joiner = ', INTERVAL '                       # joiner, work as simple python join). Example: 'expressions[0], INTERVAL expressions[1]'
    template = "%(function)s(%(expressions)s DAY)"  # template of query part
    output_field = DateField()                   # result db type

Example:

SubscriptionProduct.objects.filter(end_date=DateAdd('start_date', 'duration'))

Result:

SELECT ... 
FROM subscription_product 
WHERE end_date = DATE_ADD(start_date, INTERVAL duration DAY)

Using dateadd in django filter

3 Answers3

Linked