[Fixed]-Django time difference with F object


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.


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

This results in the following SQL:

SELECT "assignments_assignment"."id", 
       ("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.



As far as I know , you can not pass an F() object as a params to another function since F() base class is a tree.Node type, A class for storing a tree graph which primarily used for filter constructs in the ORM.

see F() define at django/db/models/expression.py and Node at django/utils/tree.py (django 1.3.4)

class ExpressionNode(tree.Node):

class F(ExpressionNode):
    An expression representing the value of the given field.
    def __init__(self, name):
        super(F, self).__init__(None, None, False)
        self.name = name

    def __deepcopy__(self, memodict):
        obj = super(F, self).__deepcopy__(memodict)
        obj.name = self.name
        return obj

    def prepare(self, evaluator, query, allow_joins):
        return evaluator.prepare_leaf(self, query, allow_joins)

    def evaluate(self, evaluator, qn, connection):
        return evaluator.evaluate_leaf(self, qn, connection)

you can do something like

Assignment.objects.filter(due_date__gt=F('due_date') - timedelta(days=1))

but not

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

Correct me if i was wrong. Hope this little help.



Just in case anyone else looks for this, here’s something that might be worth looking into.

I’m using Django 1.4 and am running into the exact same issue as the OP. Seems that the issue is probably due to timedelta and datetime needing to evaluate before being sent to the database, but the F object is inherently only going to resolve in the database.

I noticed that in Django 1.8, a new DurationField was introduced that looks like it would directly work like python’s timedelta . This should mean that instead of needing to take the timedelta of an F object look up on an IntegerField, one could theoretically use a DurationField and then the F object wouldn’t need to be in a timedelta at all. Unfortunately, due to dependencies, I’m not currently able to upgrade my project to 1.8 and test this theory.

If anyone else encounters this problem and is able to test my suggestion, I’d love to know. If I resolve my dependencies and can upgrade to 1.8, then I’ll be sure to post back with my results.

Leave a comment