[Fixed]-Aggregate difference between DateTime fields in Django

by

11👍

✅

I think since Django 1.8 we can do better:

I would like just to draw the part with annotation, the further part with aggregation should be straightforward:

from django.db.models import F, Func
SomeModel.objects.annotate(
    duration = Func(F('end_date'), F('start_date'), function='age')
)

[more about postgres age function here: http://www.postgresql.org/docs/8.4/static/functions-datetime.html ]

each instance of SomeModel will be anotated with duration field containg time difference, which in python will be a datetime.timedelta() object [more about datetime timedelta here: https://docs.python.org/2/library/datetime.html#timedelta-objects ]

👤andilabs

15👍

Almost the same solution as @andri proposed. In the final result you will get the same data.
ExpressionWrapper – New in Django 1.8.

from datetime import timedelta
from django.db.models import ExpressionWrapper, F, fields
from app.models import MyModel

duration = ExpressionWrapper(F('closed_at') - F('opened_at'), output_field=fields.DurationField())
objects = MyModel.objects.closed().annotate(duration=duration).filter(duration__gt=timedelta(seconds=2))

for obj in objects:
    print obj.id, obj.duration, obj.duration.seconds

# sample output
# 807 0:00:57.114017 57
# 800 0:01:23.879478 83
# 804 3:40:06.797188 13206
# 801 0:02:06.786300 126
👤michal-michalak

0👍

I will do it step by step:

  1. first step:annotate the timedelta
  2. group by and sum timedelta

the code like this:

from django.db.models import Count, Sum, F

times_obj_list = models.TaskTime.objects.annotate(times=F("end_time")-F("start_time"))

groupby_obj_list = times_obj_list.values("client").annotate(cnt=Count("id"),seconds=Sum(times)).order_by()
👤dengruquan

-2👍

Django currently only supports aggregates for Min, Max, Avg and Count, so using raw SQL is the only way to achieve what you want. When you use raw SQL, database-independence is out the window, so unfortunately, you’re out of luck. You’ll have to just detect the database and alter the SQL appropriately.

👤Chris Pratt

Leave a comment