Django ORM: Filter on timedelta of Datetime fields

Question

I am trying to fetch posts based on the time difference of two DateTimeFields, say, posts that were deleted in less than 10 minutes after they were posted.

class Post(models.Model):
    ...
    time_posted = models.DateTimeField()
    time_deleted = models.DateTimeField(blank=True, null=True)

With the above model in hand, I tried;

from datetime import timedelta

Post.objects.exclude(deleted__isnull=True).annotate(
    delta=F('time_deleted') - F('time_posted')
).filter(delta__lt=timedelta(minutes=10))

and got a TypeError: expected string or buffer. Then I thought it may be the change of type (DateTime objects yielding Time object) so I tried it with ExpressionWrapper:

Post.objects.exclude(deleted__isnull=True).annotate(
    delta=models.ExpressionWrapper(
        F('time_deleted') - F('time_posted'), 
        output_field=models.TimeField())
    ).filter(delta__gt=timedelta(minutes=10))

But this caused the same exception too.

Any help is much appreciated.

EDIT

According to @ivan's suggestion, I tried DurationField() instead. I no longer get the exception but the delta is always 0.

>>> post = Post.objects.exclude(deleted__isnull=True).annotate(
        delta=ExpressionWrapper(F('deleted') - F('time'), 
        output_field=DurationField())
    ).first()
>>> post.time_posted
datetime.datetime(2015, 8, 24, 13, 26, 50, 857326, tzinfo=<UTC>)
>>> post.time_deleted
datetime.datetime(2015, 8, 24, 13, 27, 30, 521569, tzinfo=<UTC>)
>>> post.delta
datetime.timedelta(0)

Answer 1

The output_field kwarg should be DurationField instead, because it stores datetime.timedelta in Django, while TimeField stores datetime.time.

There is a caveat though:

Arithmetic with DurationField works in most cases. However on all databases other than PostgreSQL, comparing the value of a DurationField to arithmetic on DateTimeField instances will not work as expected.

In the SQLite backend DurationField is represented by bigint which stores microseconds:

class DatabaseWrapper(BaseDatabaseWrapper):
    vendor = 'sqlite'
    # ...
    data_types = {
        # ...
        'DecimalField': 'decimal',
        'DurationField': 'bigint',
        'FileField': 'varchar(%(max_length)s)',
        # ...
    }

So because you are using SQLite you actually need delta to be a value in microseconds. See this answer for a Func which does that.