Django: Count objects in a particular month

Question

I have this model:

class Person(models.Model):
   city = models.CharField(max_length=20, blank=True)
   added_date = models.DateField(default=datetime.date.today)

I want to create a template/view that has a table of months and the number of people added that month (ie, 5 in january, 10 in february, 8 in march, etc.). I have a similar table for all the people from a each city using:

cities = Patient.objects.values('city').annotate(city_count=Count('city')).order_by('-city_count')

I don't know how to do that for my months table. I could filter for a particular month, then count all. But then I'd need to run that through a loop over every month, which would be multiple database hits. I don't want to do that.

Is there a way to do this without writing sql and just using django's api?

Have you considered pulling them all, working on it in Python? It's only one DB hit, but a big one — Steve Jalim, Sep 04 '10 at 17:12
I guess I could try that but I wonder if there is a more elegant way to use django to help me with this, or if not, to allow the db query to do it for me; that seems more appropriate to me. — rsp, Sep 04 '10 at 18:44

score 0 · Answer 1 · answered Jan 28 '20 at 07:58

Its a very old thread, but i guess I'll answer in case someone else ended up here looking for a solution. The solution is for Django 1.10+ using the ExtractMonth function, for more detail visit official documentation

First you have to import ExtractMonth, like

from django.db.models.functions import ExtractMonth

Then using your Persons model, the code will be like this

personsMonthlyData = Person.objects.annotate(month=ExtractMonth('added_date')).values('month').annotate(count=Count('id')).order_by('month')

personsMonthlyData will output something like this

[{month: 1, count: 3}, {month: 2: count: 1}]

where month represent the month number e.g. 1 for January and 2 for February and the count against each month is assigned to the count item.

I hope this helps.

score -1 · Answer 2 · answered Sep 04 '10 at 19:11

The fact that most DBs have such a perfect way of doing this with a GROUP BY query that has no analog in Django AFAIK would lead me to drop into SQL to do this. I did a google search for "django sql" and turned up this post by Doug Hellman on this exact problem: http://blog.doughellmann.com/2007/12/using-raw-sql-in-django.html. I would use that as a starting point for getting your month counts into Django.

Django: Count objects in a particular month

2 Answers2