[Fixed]-Group by weeks, months, days

24👍

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")

1👍

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

👤Marat

Leave a comment