How to calculate the duration using startTime & endTime fields in a QuerySet in Django?

Question

I have this model:

class TimeInterval(models.Model):
    startTime = models.DateTimeField()
    endTime = models.DateTimeField()

How can I aggregate average time interval using only the QuerySet API?

I tried this:

qs = TimeInterval.objects.extra(
    select={"duration": "endTime - startTime"}).aggregate(
        Avg("duration"))

but it throws:

FieldError: Cannot resolve keyword 'duration' into field. Choices are:
endTime, startTime

Django doesn't support aggregation over fields added with `extra`. The answers to [this question](http://stackoverflow.com/questions/4567543/using-aggregate-on-a-value-introduced-using-extraselect-in-a-django) contain some suggestions for how to work around this limitation. — Gareth Rees, Aug 27 '12 at 21:06
possible duplicate of [Using .aggregate() on a value introduced using .extra(select={…}) in a Django Query?](http://stackoverflow.com/questions/4567543/using-aggregate-on-a-value-introduced-using-extraselect-in-a-django) — Gareth Rees, Aug 27 '12 at 21:07
@GarethRees, yes is the same question replacing STRFTIME by [DATEDIFF](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff) — dani herrera, Aug 27 '12 at 21:15
another work around relies on the fact that `Avg(duration)` equals to `Sum(endTime) - Sum(startTime) / Count` or, almost, `Avg(endTime) - Avg(startTime)` — okm, Aug 28 '12 at 06:04

score 1 · Answer 1 · answered Aug 27 '12 at 21:10

1

I'll use a workaround for this.

Create a new field called duration.

Create a pre-save_signal for TimeInterval that would calculate the value for duration.

Then, you can easily use the duration field in your queries.

answered Aug 27 '12 at 21:10

dannyroa

1 Answers1