Zamphatta Zamphatta - 1 year ago 119
Python Question

Django Group By Weekday?

I'm using Django 1.5.1, Python 3.3.x, and can't use raw queries for this.

Is there a way to get a QuerySet grouped by weekday, for a QuerySet that uses a date

filter? I'm trying to group results by weekday, for a query that ranges between any two dates (could be as much as a year apart). I know how to get rows that match a weekday, but that would require pounding the DB with 7 queries just to find out the data for each weekday.

I've been trying to figure this out for a couple hours by trying different tweaks with the
filter, but nothing's working. Even Googling doesn't help, which makes me wonder if this is even possible. Any Django guru's here know how, if it is possible to do?

Answer Source

You need to add an extra weekday field to the selection, then group by that in the sum or average aggregation. Note that this becomes a database specific query, because the 'extra' notation becomes passed through to the DB select statement.

Given the model:

class x(models.Model):
    date = models.DateField()
    value = models.FloatField()

Then, for mysql, with a mapping of the ODBC weekday to the python datetime weekday:

x.objects.extra(select={'weekday':"MOD(dayofweek(date)+5,7)"}).values('weekday').annotate(weekday_value=Avg('value'), weekday_value_std=StdDev('value'))

Note that if you do not need to convert the MySql ODBC weekday (1 = Sunday, 2 = Monday...) to python weekday (Monday is 0 and Sunday is 6), then you do not need to do the modulo.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download