I need to count objects in a queryset by year and month

[Mahmood Khan]

Currently, I am doing something along the lines of:
Item.objects.all().\

extra(select={'year': "EXTRACT(year FROM date)"}).\
values('year').annotate(count_items=Count('date'))

[Simon Charette]

Hi mahmoodkhan,

You don't extra() to achieve this. There's already a couple of open tickets that you can relate to (#25774 comes to mind) but you should annotate your queryset with a ​Transform in order to extract the year and months.

If you don't want to write your own Transform subclass you can use the undocumented MonthTransform and YearTransform to annotate your queryset:

from django.db.models.lookups import MonthTransform as Month, YearTransform as Year

Item.objects.annotate(
    year=Year('date'),
    month=Month('date'),
).values('year', 'month').annotate(count=Count('pk'))

Keep in mind that you're relying on a private API (used internally to implement the __month and __year lookups) that might be moved around or replaced.

Once values() allows transforms and lookups you should be able to simply do this:

Item.objects.values('date__year', 'date__month').annotate(count=Count('pk'))

[Mahmood Khan]

Thanks! much appreciated.

Replying to charettes:

Hi mahmoodkhan,

You don't extra() to achieve this. There's already a couple of open tickets that you can relate to (#25774 comes to mind) but you should annotate your queryset with a ​Transform in order to extract the year and months.

If you don't want to write your own Transform subclass you can use the undocumented MonthTransform and YearTransform to annotate your queryset:

from django.db.models.lookups import MonthTransform as Month, YearTransform as Year

Item.objects.annotate(
    year=Year('date'),
    month=Month('date'),
).values('year', 'month').annotate(count=Count('pk'))

Keep in mind that you're relying on a private API (used internally to implement the __month and __year lookups) that might be moved around or replaced.

Once values() allows transforms and lookups you should be able to simply do this:

Item.objects.values('date__year', 'date__month').annotate(count=Count('pk'))

[Rich Rauenzahn]

FYI:

MonthTransform and YearTransform are gone now in Django 1.10.

You can use TruncMonth/TrunchYear from django.db.models.functions.

I believe it is slightly different -- I had to modify my html template to filter the value through |date: 'Y' and |date: 'm', otherwise I got something like "2016, midnight" rather than "2016"

See:

​http://stackoverflow.com/questions/8746014/django-group-by-date-day-month-year
https://code.djangoproject.com/ticket/26649
​https://docs.djangoproject.com/en/1.10/ref/models/database-functions/#trunc