Django ORM: How can I filter based on an annotation timedelta result

Question

I have a model like this:

class Interval(models.Model):
   start = models.Datetime()
   end = models.Datetime(null=True)

I would like to query all intervals that are larger then 5 minutes. I'am able to do intervals=Interval.objects.exclude(end=None).annotate(d=models.F("end")-models.F("start"))

When I do intervals[0].d , I have the interval, which is correct. Now I would like to only get as results the entries where d is greater than 5 minutes.

I tried intervals=Interval.objects.exclude(end=None).annotate(d=models.F("end")-models.F("start")).filter(d__gt=timedelta(0, 300)), but I get the following error: TypeError: expected string or bytes-like object. It tries to match the timedelta with a regex of datetime. `

Any ideas?

Thanks in advance

Answer 1

I think the problem is that you have to specify the type of the annotated column to Django as a DurationField. So you can write it like:

from datetime import timedelta
from django.db.models import ExpressionWrapper, F, DurationField

delta = ExpressionWrapper(F("end")-F("start"), DurationField())

intervals = (Interval.objects.exclude(end=None)
                             .annotate(d=delta)
                             .filter(d__gt=timedelta(0, 300)))

This will construct a query like:

SELECT id, start, end TIMESTAMPDIFF(MICROSECOND, start, end) AS `d`
FROM interval
WHERE TIMESTAMPDIFF(MICROSECOND, start, end) > 300000000

But we here thus give Django a hint how to interpret the d field (as a DurationField), and thus how to "serialize" the timedelta object).