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.

Possible duplicate of [Django: Group by date (day, month, year)](http://stackoverflow.com/questions/8746014/django-group-by-date-day-month-year) — tback, Jun 24 '16 at 07:27

score 31 · Answer 1 · edited Aug 20 '21 at 09:00

31

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.

edited Aug 20 '21 at 09:00

djvg

11,722
5
72
103

answered Sep 29 '16 at 16:31

mycroft

310
1
3
5

3

@mycroft from what I can see, this was added in 1.10 – Ian Clark Nov 03 '16 at 16:32
indeed - thanks @IanClark https://docs.djangoproject.com/en/1.10/ref/models/database-functions/#module-django.db.models.functions.datetime – ptim Jul 19 '17 at 16:13
Should you use this, or Extract? https://docs.djangoproject.com/en/4.0/ref/models/database-functions/#extract – gabn88 Jul 25 '22 at 16:35

score 12 · Accepted Answer · edited Apr 26 '16 at 17:29

12

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

edited Apr 26 '16 at 17:29

gregoltsov

2,269
1
22
37

answered Feb 17 '14 at 21:38

frnhr

12,354
9
63
90

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 Feb 17 '14 at 21:43
Same error "missing FROM-clause entry for table..." – Yuval Adam Feb 17 '14 at 22:16
Can you post your models? I have this working on a simple case with no such error. – frnhr Feb 17 '14 at 23:55
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! – Yuval Adam Feb 18 '14 at 08:32
This doesn't work. It groups by date field but not by year. – Andrii Zarubin May 18 '16 at 04:56
Seems like extra() is being deprecated. What is the official way of doing this without extra()? – Mad Wombat Jun 02 '16 at 18:20
@MadWombat Where did you see extra() being deprecated? I can't find it mentioned in the official docs: https://docs.djangoproject.com/en/dev/ref/models/querysets/#django.db.models.query.QuerySet.extra – frnhr Jun 03 '16 at 09:23
@frnhr right on the page that you linked it says "Use this method as a last resort. This is an old API that we aim to deprecate at some point in the future. Use it only if you cannot express your query using other queryset methods. If you do need to use it, please file a ticket using the QuerySet.extra keyword with your use case" – Mad Wombat Jun 03 '16 at 13:57

Annotate (group) dates by month/year in Django

2 Answers2

Linked