Django Group By Weekday?

Question

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 __range 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 __week_day 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?

score 10 · Accepted Answer · answered Dec 01 '16 at 21:59

Since extra is deprecated, here is a new way of grouping on the day of the week using ExtractDayOfWeek.

from django.db.models.functions import ExtractWeekDay
YourObjects.objects
    .annotate(weekday=ExtractWeekDay('timestamp')) 
    .values('weekday')                          
    .annotate(count=Count('id'))                  
    .values('weekday', 'count')

This will return a result like:

[{'weekday': 1, 'count': 534}, {'weekday': 2, 'count': 574},.......}

It is also important to note that 1 = Sunday and Saturday = 7

And 5 versions of django later, the `ExtractWeekDay` keyword is added. Note: still need to use the .extra() function in django 1.5 (as asked). — tmarthal, Dec 29 '16 at 02:41

Victor Castillo Torres · Answer 2 · 2013-06-22T23:36:08.800

Well man I did an algorithm this one brings you all the records since the beginning of the week (Monday) until today

for example if you have a model like this in your app:

from django.db import models

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

from datetime import datetime
from myapp.models import x
start_date = datetime.date(datetime.now())
week = start_date.isocalendar()[1]
day_week =start_date.isoweekday()
days_quited = 0
less_days = day_week
while less_days != 1:
     days_quited += 1
     less_days -= 1

week_begin = datetime.date(datetime(start_date.year,start_date.month,start_date.day-days_quited))

records = x.objects.filter(date__range=(week_begin, datetime.date(datetime.now())))

And if you add some records in the admin with a range between June 17 (Monday) and June 22 (today) you will see all those records, and if you add more records with the date of tomorrow for example or with the date of the next Monday you will not see those records.

If you want the records of other week unntil now you only have to put this:

start_date = datetime.date(datetime(year, month, day))
records = x.objects.filter(date__range=(week_begin, datetime.date(datetime.now())))

Hope this helps! :D

That's just a date range though, not a grouping. It won't work when the date range spans multiple weeks. — Zamphatta, Jun 22 '13 at 23:27
Well I think this is the equivalent to `Model.objects.filter(date__week=week_number)`, Multiple weeks you mean consecutives? — Victor Castillo Torres, Jun 22 '13 at 23:31

score 0 · Answer 3 · answered Oct 04 '13 at 00:14

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.

score 0 · Answer 4 · answered Jul 23 '14 at 14:34

For model like this:

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

You can use query:

weekday     = {"w": """strftime('%%w', date)"""}
qs = A.objects.extra(select=weekday).values('w').annotate(stat = Sum("value")).order_by()

Django Group By Weekday?

4 Answers4