Annotate (group) dates by month/year in Django

Question

Using the Django DateQuerySet I'm pulling related years for item objects from a Group query.

>>> Group.objects.all().dates('item__date', 'year')
[datetime.date(1990, 1, 1), datetime.date(1991, 1, 1), ...(remaining elements truncated)...']

Now I want to perform a count by distinct year on these dates. I thought this would work:

>>> Group.objects.all().dates('item__date', 'year').annotate(Count('year'))
FieldError: Cannot resolve keyword 'year' into field.

But looks like I'm missing something. How can I fix this query?

I've also tried this query:

>>> (Group
 .objects
 .all()
 .extra(select=
 {'year': 
 connections[Group.objects.db].ops.date_trunc_sql('year', 'app_item.date')}))
ProgrammingError: missing FROM-clause entry for table "app_item" LINE 1: SELECT (DATE_TRUNC('year', app_item.date)) AS...

But that doesn't work either.

• tback: Possible duplicate of Django: Group by date (day, month, year)

Answer 1

For anyone finding this after django 1.9 there is now a TruncDate (TruncMonth, TruncYear) that will do this.

from django.db.models.functions import TruncDate
(Group.objects.all().annotate(date=TruncDate('your_date_attr')
 .values('date')
 .annotate(Count('items'))

Hope it helps.

• Ian Clark: @mycroft from what I can see, this was added in 1.10
• ptim: indeed - thanks @IanClark docs.djangoproject.com/en/1.10/ref/models/database-functions/…
• gabn88: Should you use this, or Extract? docs.djangoproject.com/en/4.0/ref/models/database-functions/…

Answer 2

Try something along these lines:

from django.db.models import Count
Item.objects.all().\
 extra(select={'year': "EXTRACT(year FROM date)"}).\
 values('year').\
 annotate(count_items=Count('date'))

You might want to use item_instance._meta.fields instead of manually specifying "date" in the MySQL statement there...

Also, note that I started with Item QuerySet instead of Group, for the sake of simplicity. It should be possible to either filter the Item QuerySet to get the desired result, or to make the extra bit of MySQL more complicated.

EDIT:

This might work, but I'd definitely test the guts out of it before relying on it :)

Group.objects.all().\
 values('item__date').\
 extra(select={'year': "EXTRACT(year FROM date)"}).\
 values('year').\
 annotate(count=Count('item__date'))

• Yuval Adam: Well, the Group query is what makes this interesting. I'm already doing other stuff related to it, so I can't just start from the Item.
• Yuval Adam: Same error "missing FROM-clause entry for table..."
• frnhr: Can you post your models? I have this working on a simple case with no such error.
• Yuval Adam: My models are slightly complicated, but I'm happy to hear it works for you. I'll see if I can get it to work on my side. Thanks!
• Andrii Zarubin: This doesn't work. It groups by date field but not by year.