Group by weeks, months, days

Question

I would like to group querysets by a date interval according to a datetime attribute of the model. I want to show average values from the model on a day-to-day, week-by-week and month-by-month basis.

E.g.

• Week commencing 01/01/2017 - average distance: 30
• Week commencing 08/01/2017 - average distance: 40 ...

Can this be achieved with the standard Django queryset API?

Answer 1

Assuming the following model which might match your description

class Activity(models.Model):
    timestamp = models.DateTimeField(auto_now_add=True, blank=True)
    distance  = models.IntegerField()

You can achieve a week by week statistic with the following query

from django.db.models.functions import ExtractWeek, ExtractYear
from django.db.models import Sum, Count

stats = (Activity.objects
    .annotate(year=ExtractYear('timestamp'))
    .annotate(week=ExtractWeek('timestamp'))
    .values('year', 'week')
    .annotate(avg_distance=Avg('distance'))
)

Sample output

<QuerySet [{'year': 2018, 'week': 31, 'distance': 3.2}]>

To recover the first day of week, check Get date from week number

In particular:

for record in stats:
    week = "{year}-W{week}-1".format(year=record['year'], week=record['week'])
    timestamp = datetime.datetime.strptime(week, "%Y-W%W-%w")

Answer 2

There are a bunch of field lookups specifically for date/datetime fields: week, day, month (should be combined with year) etc.