12

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?

zubhav
  • 1,519
  • 1
  • 13
  • 19

2 Answers2

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")
Andrei Cioara
  • 3,404
  • 5
  • 34
  • 62
  • 1
    Great answer ! Thanks a lot – Samuel Dauzon Dec 11 '18 at 23:33
  • 1
    Great answer, saved me a lot of time! The only problem is python week numbering vs Postgres/ISO. 2020-01-01 in python is in week 0: ` datetime.datetime(2020,1,1).strftime("%Y-W%W-%w") '2020-W00-3'` while in Postgres it is in week 1: ` select extract('week' from '2020-01-01'::date) as week; week ------ 1 `; https://docs.python.org/3.8/library/datetime.html#strftime-and-strptime-format-codes https://en.wikipedia.org/wiki/ISO_week_date https://www.postgresql.org/docs/12.4/functions-datetime.html#week – user1685805 Apr 19 '20 at 21:18
1

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

Marat
  • 15,215
  • 2
  • 39
  • 48