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.

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.

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