Django averaging over a date range

Question

I have a simple uptime monitoring app built in Django which checks if a site doesn't respond to a ping request. It stores a timestamp of when it was pinged in the column "dt" and the millisecond response time in the column "ms". The site is pinged every minute and an entry is put into the database.

The Django model looks like this:

class Uptime (models.Model):
    user = models.ForeignKey(User)
    dt = models.DateTimeField()
    ms = models.IntegerField()

I'd like to grab one day at a time from the dt column and take an average of the ms response time for that day. Even though there's 1440 entries per day I'd like to just grab the day (e.g. 4-19-2013) and get the average ms response time. The code I have been using below is undeniably wrong but I feel like I'm on the right track. How could I get this to work?

       output_date = Uptime.objects.filter(user = request.user).values_list('dt', flat = True).filter(dt__range=(startTime, endTime)).order_by('dt').extra(select={'day': 'extract( day from dt )'}).values('day')
       output_ms = Uptime.objects.filter(user = request.user).filter(dt__range=(startTime, endTime)).extra(select={'day': 'date( dt )'}).values('day').aggregate(Avg('ms'))

Thanks!

Answer 1

You need to annotate to do the group by. Django doesnt have anything in the orm for extracting only dates, so you need to add an "extra" parameter to the query, which specifies to only use the dates. You then select only those values and annotate.

Try the following:

Uptime.objects.filter(user=request.user).extra({'_date': 'Date(dt)'}).values('_date').annotate(avgMs=Avg('ms'))

This should give you a list like follows:

[{'_date': datetime.date(2012, 7, 5), 'avgMs': 300},{'_date': datetime.date(2012, 7, 6), 'avgMs': 350}]