python - Django filtering model on datetime with interger and datetime

Question

I try to make a query to select all the object that where modified since a specified time This time is now - max_schedule_delay where max_schedule_delay is a data from the object (see code sample below).

I try multiple thing .. but here I am. Maybe you will be able to help me find a way.

Environment

• python 2.7
• django 1.11

database : Posgresql

Sample code

from django.db import models

class MyObject(models.Model):
    # last modification date
    mtime = models.DateTimeField(auto_now=True, db_index=True)
    # maximum delay before rescheduling in seconds
    max_schedule_delay = models.IntegerField()

What I want to achieve

select * from MyObject where (mtime + max_schedule_delay > now)

My tests

from django.db.models import F, ExpressionWrapper,, TimeField, DateTimeField
from django.db.models.functions import Cast
import datetime

now = datetime.datetime.now()

MyObject.objects.filter(max_schedule_delay__lte=now - F("mtime")) # here max_schedule_delay is a integer, so this query is not possible

# I try to split this query in two, but still not wotking
MyObject.objects.annotate(threshold=ExpressionWrapper(now - F("max_schedule_delay"), output_field=DateTimeField())).filter(mtime__gte=F("threshold"))

MyObject.objects.annotate(threshold=ExpressionWrapper(F("mtime") + F("max_schedule_delay"), output_field=DateTimeField())).filter(threshold__gte=now)

MyObject.objects.annotate(as_date=Cast("max_schedule_delay", TimeField()))

Any help is welcome, Thanks !

Answer 1

After some more research, and based on the post Using dateadd in django filter I was able to do something, but only for posgresql

from django.db.models import Func, DateTimeField
import datetime

class DateAdd(Func):
    """
    Custom Func expression to add date and int fields as day addition
    Usage:
    Model.objects.annotate(annotation=DateAdd('field_date','field_seconds'))
    """
    arg_joiner = " + CAST("
    template = "%(expressions)s || ' seconds' as INTERVAL)"
    output_field = DateTimeField()

MyModel.objects.annotate(
        threshold=DateAdd("mtime", "max_schedule_delay")
    ).filter(
        max_schedule_delay__isnull=False,
        state=botSession.STATE.DONE,
        threshold__lte=datetime.datetime.now()
    )