Django group by month and year doesnt work

Question

I've already read this Django: Group by date (day, month, year) and all related stuff by googling "django group by month"

If I try the "cleanest" solution - using Django 1.11, I end up with this:

class Request(BaseModel):
    date_creation = models.DateTimeField(default=None,
                                         blank=True, null=True)

print([v for v in
       Request.objects.annotate(month=ExtractMonth('date_creation'),
                                year=ExtractYear('date_creation'),)
                      .values('month', 'year')
                      .annotate(total=Count('month'))
                      .values('month', 'year', 'total')
       ])

And the result doesn't do a group by! I get this:

[{'month': 6, 'year': 2017, 'total': 1}, 
 {'month': 7, 'year': 2017, 'total': 1}, 
 {'month': 7, 'year': 2017, 'total': 1}]

I need to get:

[{'month': 6, 'year': 2017, 'total': 1}, 
 {'month': 7, 'year': 2017, 'total': 2}]

I've also tried:

print([v for v in
       Request.objects.extra({'month': 'strftime("%m", date_creation)',
                              'year': 'strftime("%Y", date_creation)'})
                      .values('month', 'year')
                      .annotate(total=Count('*'))
                      .values('month', 'year', 'total')
       ])

And then I get:

[{'month': '06', 'year': '2017', 'total': 1},
 {'month': '07', 'year': '2017', 'total': 1},
 {'month': '07', 'year': '2017', 'total': 1}]

Any idead?

score 7 · Accepted Answer · edited May 27 '22 at 19:01

I found the problem thanks to PyCharm. I seriously dont know how I could have found the solution without that IDE. The more I'm using it the more I find it powerful. Then I found the solution thanks to this: How can I remove Model Meta ordering in Django or get the original queryset from the built in Manager?

My Request model has a parent which has a field date_creation and a class Meta: with ordering = ['date_creation'].

So if you don't add order_by('field_xx') in your query, then Django automagically add this: order_by('date_creation').

Thus my query looked like:

SELECT
    (strftime("%m", date_creation)) AS "month",
    (strftime("%Y", date_creation)) AS "year",
    COUNT(*) AS "total" FROM "app_request"
  GROUP BY
    (strftime("%m", date_creation)),
    (strftime("%Y", date_creation)),
    "app_request"."date_creation"

And it broke the query.

The solution was:

from django.db.models.functions.datetime import ExtractMonth, ExtractYear
print([v for v in
       Request.objects.annotate(month=ExtractMonth('date_creation'),
                                year=ExtractYear('date_creation'),)
                      .order_by()
                      .values('month', 'year')
                      .annotate(total=Count('*'))
                      .values('month', 'year', 'total')
       ])

Actually, my solution was working from the very beginning!

I had the same issue. My queryset was `SomeClass.objects.annotate(month=ExtractMonth('created_at')).values('month').annotate(users=Count('id'))` So by looking at your answer, I edited my queryset like this `SomeClass.objects.annotate(month=ExtractMonth('created_at')).values('month').annotate(users=Count('id')).order_by('created_at__month')` For me the order_by('created_at__month') after the last annotate worked. Thanks — Fahad Ali, Jan 27 '20 at 13:08

score 0 · Answer 2 · answered Jul 13 '17 at 15:48

Here is what one of my queries looks like for grouping by hour:

MyDateObject.objects.filter(**kwargs)\
                    .extra({ "hour": "date_part('hour', timestamp AT TIME ZONE '%s')" % (ctz.zone) })\
                    .values("hour")\
                    .annotate(Count("transaction", distinct=True))

The difference between mine and yours is I'm using the extra function. I would imagine you would have to do something similar instead of your first annotate like this:

Request.objects.extra({ "month": ExtractMonth('date_creation'),
                        "year": ExtractYear('date_creation') })
               .values('month', 'year')
               .annotate(total=Count('month'))
               .values('month', 'year', 'total')

Note: I'm using Django 1.9.

edit: The more I look at this, maybe it's the distinct=True in my count that really makes it work.

If I try your solution, I get an error: `django.db.utils.OperationalError: no such function: F`. The query that is tried is `SELECT (ExtractMonth(F(date_creation))) AS "month", (ExtractYear(F(date_creation))) AS "year", COUNT(*) AS "total" FROM "app_request" GROUP BY (ExtractMonth(F(date_creation))), (ExtractYear(F(date_creation))), "app_request"."date_v_debut" ORDER BY "app_request"."date_v_debut" ASC` — Olivier Pons, Jul 14 '17 at 07:24
I've updated my question. My latest query is very close to yours. — Olivier Pons, Jul 14 '17 at 07:29

Django group by month and year doesnt work

2 Answers2