Annotate sum of DateTimeField and IntegerField in Django ORM?

Question

Given:

from django.conf import settings
from django.db import models

class Membership(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL)
    started = models.DateTimeField()
    num_weeks = models.IntegerField()

How can I get a QuerySet of all memberships that haven't expired?

I want to write something like this...

from datetime import datetime, timedelta
Membership.objects.annotate(
    ended=F('started') + datetime.timedelta(weeks=F('weeks'))
).filter(ended__gt=datetime.today())

But I doubt the F expressions are compatible with timedeltas, and even if they were, timedelta(weeks=) is not compatible with F expressions, so this code fails. I'm guessing I need to convert the IntegerField to some type of database-level duration type but I can't find a clue as to how to do such a thing in the docs.

Answer 1

I faced this problem today. For those looking for the answer, there is a solution for this.

For Postgresql, you can use the INTERVAL function for this purpose.

First of all add a databse funcion with django Func class:

class IntervalSeconds(Func):

    function = 'INTERVAL'
    template = "(%(expressions)s * %(function)s '1 weeks')"

then import and use this function in your code:

Membership.objects.annotate(
    ended=F('started') + IntervalSeconds(F('weeks'))
).filter(ended__gt=datetime.today())

For other options like days, hours, minutes, etc, you can implement them this way or you can customize this class to get type when using it.

I hope this helped after 5 years!

---

Reference: https://stackoverflow.com/a/57734565

You can find Postgres INTERVAL here:

https://www.postgresql.org/docs/9.1/datatype-datetime.html

https://www.postgresql.org/docs/9.6/functions-datetime.html

Also, you can see this great tutorial:

https://www.postgresqltutorial.com/postgresql-interval/