2

Lets say i have 100 objects in db and each object has created_at and done_at time. I want to calculate average of duration for all objects. Have idea like this: first to calculate durations of all objects and then get average. But is there other good way that will help?

MRustamzade
  • 1,425
  • 14
  • 27
  • 1
    you can also use the standard deviation as well have a look here [link](https://medium.com/@hakibenita/9-django-tips-for-working-with-databases-beba787ed7d3) – Hozayfa El Rifai Jun 28 '18 at 12:41

3 Answers3

5

You can use aggregate() and F expressions for that:

from django.db.models import Avg, F

Model.objects.aggregate(average_delta=Avg(F('done_at') - F('created_at')))
Jieter
  • 4,101
  • 1
  • 19
  • 31
  • Thanks for helping. The problem is that i am using `sqlite3` and fields not as `date/time` saved as text. :/ – MRustamzade Jun 28 '18 at 12:42
  • 1
    Did not test this, but there seems to be a solution: https://stackoverflow.com/a/32686206/517560 – Jieter Jun 28 '18 at 13:25
2

Apart from this answer, You could try this also

from django.db.models import Avg, F

Model.objects.annotate(diff=F('done_at') - F('created_at')).aggregate(duration=Avg('diff'))
JPG
  • 82,442
  • 19
  • 127
  • 206
  • Thanks for helping. Can't solve problem with converting string to datetime for avg – MRustamzade Jun 28 '18 at 12:51
  • I think that's hard for you. Anyway try [Django Cast()](https://docs.djangoproject.com/en/2.0/ref/models/database-functions/#cast) – JPG Jun 28 '18 at 12:59
  • Thanks alot, i did at first like you wrote but problem was that i have tried to cast it with `DateTimeField` :) – MRustamzade Jun 28 '18 at 14:26
0

Using Jieter and Jerin Peter George answer made little changes and it works perfect:

from django.db.models import Avg, F, Sum, DurationField

IssueDone.objects.annotate(time_elapsed=Sum(Cast(F('done_date')-F('created_date'), DurationField()))).aggregate(Avg('time_elapsed'))
MRustamzade
  • 1,425
  • 14
  • 27