Django time difference with F object

Question

I have the following model:

class Assignment(models.Model):
  extra_days = models.IntegerField(default=0)
  due_date = models.DateTimeField()

Where due_date is the date the assignment is due and extra_days is the number of extra days given after the due date to finish the assignment.

I want to create a query that returns all rows where due_date + extra_days is greater than the current date. Here's what I am doing:

from django.utils import timezone
from django.db.models import F
from datetime import datetime

cur_date = timezone.make_aware(datetime.now(), timezone.get_default_timezone())
a = Assignment.objects.filter(extra_days__gt=cur_date - F('due_date'))

When I print a, I get the following error:

  File "c:\Python27\lib\site-packages\MySQLdb\cursors.py", line 204, in execute
    if not self._defer_warnings: self._warning_check()
  File "c:\Python27\lib\site-packages\MySQLdb\cursors.py", line 117, in _warning
_check
    warn(w[-1], self.Warning, 3)
Warning: Truncated incorrect DOUBLE value: '2013-09-01 02:54:31'

If I do a time difference that results in, say, 3.1 days, I'm assuming the days difference would be still be 3. I think it would more correct to do something like this:

a = Assignment.objects.filter(due_date__gt=cur_date - timedelta(days=F('extra_days')))

But that also results in an error.

How can I do this without writing a raw SQL query?

Answer 1

This depends on the database backend you are using, which seems to be PostgreSQL.

PostgreSQL can subtract dates directly, so the following will work:

from django.db.models import F, Func
from django.db.models.functions import Now

class DaysInterval(Func):
    function = 'make_interval'
    template = '%(function)s(days:=%(expressions)s)'

qs = Assignment.objects.annotate(remaining_days=F('due_date') - Now())
qs.filter(remaining_days__lt=DaysInterval(F('extra_days')))

This results in the following SQL:

SELECT "assignments_assignment"."id", 
       "assignments_assignment"."extra_days", 
       "assignments_assignment"."due_date", 
       ("assignments_assignment"."due_date" - STATEMENT_TIMESTAMP()) AS "remaining_days" 
FROM   "assignments_assignment" 
WHERE  ("assignments_assignment"."due_date" - STATEMENT_TIMESTAMP())
        < (make_interval(DAYS:="assignments_assignment"."extra_days"))

For date difference calculations in other database backends see the Datediff function created by Michael Brooks.

Answer 2

It seems like what I'm trying to do is not possible. I ended up writing a raw query:

cursor.execute("SELECT * FROM app_assignment WHERE DATE_ADD(due_date, INTERVAL extra_days DAYS) > utc_timestamp()")

I was so repulsed at not being able to use the ORM for doing something so seemingly simple that I considered trying out SQLAlchemy, but a raw query works fine. I always tried workarounds to make sure I could use the ORM, but I'll use raw SQL going forwards for complex queries.