How to aggregate computed field with django ORM? (

Question

I'm trying to find the cumulated duration of some events, 'start' and 'end' field are both django.db.models.DateTimeField fields.

What I would like to do should have been written like this:

from django.db.models import F, Sum
from my.models import Event
Event.objects.aggregate(anything=Sum(F('start') - F('end')))
# this first example return: 
# AttributeError: 'ExpressionNode' object has no attribute 'split'

# Ok I'll try more SQLish:
Event.objects.extra(select={
                      'extra_field': 'start - end'
                    }).aggregate(Sum('extra_field'))
# this time:
# FieldError: Cannot resolve keyword 'extra_field' into field.

I can't agreggate (Sum) start and end separately then substract in python because DB can't Sum DateTime objects.

A good way to do without raw sql?

Answer 1

This answer don't realy satisfy me yet, my current work around works but it's not DB computed...

reduce(lambda h, e: h + (e.end - e.start).total_seconds(), events, 0)

It returns the duration of all events in the queryset in seconds

Better SQL less solutions?

Answer 2

If you are on Postgres, then you can use the django-pg-utils package and compute in the database. Cast the duration field into seconds and then take the sum

from pg_utils import Seconds
from django.db.models import Sum

Event.objects.aggregate(anything=Sum(Seconds(F('start') - F('end'))))

Answer 3

Can't help Christophe without a Delorean, but I was hitting this error and was able to solve it in Django 1.8 like:

total_sum = Event.objects\
    .annotate(anything=Sum(F('start') - F('end')))\
    .aggregate(total_sum=Sum('anything'))['total_sum']

When I couldn't upgrade all my dependencies to 1.8, I found this to work with Django 1.7.9 on top of MySQL:

totals = self.object_list.extra(Event.objects.extra(select={
    'extra_field': 'sum(start - end)'
})[0]