Django annotate groupings by month

Question

I have a very basic model:

class Link(models.Model):
    title = models.CharField(max_length=250, null=False)
    user = models.ForeignKey(User)
    url = models.CharField(max_length=250, blank=True, null=True)
    link_count = models.IntegerField(default=0)
    pub_date = models.DateField(auto_now_add=True)
    updated = models.DateTimeField(auto_now=True)

I can create a list of all the entries grouped by date using:

Link.objects.values('pub_date').order_by('-pub_date').annotate(dcount=Count('pub_date'))

This will naturally group items by day. But what I really want to do is group by month. Is there anyway I can do this using annotate()?

Many thanks,

G

Not an answer, but you might take a looks at this ticket: http://code.djangoproject.com/ticket/10302 — Tomasz Zieliński, Aug 22 '10 at 22:07

score 16 · Accepted Answer · edited Mar 24 '14 at 15:08

16

If you're on PostgreSQL, the following might work:

from django.db.models import Count

Link.objects.extra(select={'month': 'extract( month from pub_date )'}).values('month').annotate(dcount=Count('pub_date'))

I'm not sure how portable extract is across other databases.

edited Mar 24 '14 at 15:08

gregoltsov

2,269
1
22
37

answered Aug 22 '10 at 21:54

ars

120,335
23
147
134

Thanks! This does seem to work for mySQL also. Except this groups all months across any year. I am trying to get grouped months for each year. But this is a great start so I'll tweak. Thanks again. – givp Aug 23 '10 at 18:03
you can slap a filter in there that limits the dates to certain ranges. `.filter(pub_date__gte=datetime.datetime(fill in date here))` – boatcoder Apr 04 '12 at 17:28
4

using `connection.ops.date_trunc_sql('month', 'date')` instead of hardcoded extract SQL would made the statement compatible with others backends – jujule Feb 16 '13 at 22:21

score 14 · Answer 2 · edited Aug 12 '13 at 12:00

14

from django.db import connections
from django.db.models import Count

Link.objects.extra(select={'month': connections[Link.objects.db].ops.date_trunc_sql('month', 'pub_date')}).values('month').annotate(dcount=Count('pub_date'))

edited Aug 12 '13 at 12:00

akaihola

26,309
7
59
69

answered Nov 04 '10 at 09:44

sshwsfc

141
1
2

1

Note that you must also import `from django.db.models import Count` – Dana Woodman May 19 '12 at 19:05

score 3 · Answer 3 · answered Apr 21 '16 at 08:11

To add, as an alternative for using extra(): since Django 1.8, you can also use conditional expressions.

>>> year_overview = Link.objects.filter(pub_date__year=year).aggregate(
    jan=Sum(
        Case(When(created__month=0, then=1),
             output_field=IntegerField())
    ),
    feb=Sum(
        Case(When(created__month=1, then=1),
             output_field=IntegerField())
    ),
    mar=Sum(
        Case(When(created__month=2, then=1),
             output_field=IntegerField())
    ),
    apr=Sum(
        Case(When(created__month=3, then=1),
             output_field=IntegerField())
    ),
    may=Sum(
        Case(When(created__month=4, then=1),
             output_field=IntegerField())
    ),
    jun=Sum(
        Case(When(created__month=5, then=1),
             output_field=IntegerField())
    ),
    jul=Sum(
        Case(When(created__month=6, then=1),
             output_field=IntegerField())
    ),
    aug=Sum(
        Case(When(created__month=7, then=1),
             output_field=IntegerField())
    ),
    sep=Sum(
        Case(When(created__month=8, then=1),
             output_field=IntegerField())
    ),
    oct=Sum(
        Case(When(created__month=9, then=1),
             output_field=IntegerField())
    ),
    nov=Sum(
        Case(When(created__month=10, then=1),
             output_field=IntegerField())
    ),
    dec=Sum(
        Case(When(created__month=11, then=1),
             output_field=IntegerField())
    ),
)

>>> year_overview
{'mar': None, 'feb': None, 'aug': None, 'sep': 95, 'apr': 1, 'jun': None, 'jul': None, 'jan': None, 'may': None, 'nov': 87, 'dec': 94, 'oct': 100}

With the latest Django version I guess this is the solution recommeded — Sudhakaran Packianathan, Jun 17 '16 at 05:15

score 3 · Answer 4 · answered Nov 10 '16 at 20:27

I've read that .extra() will be deprecated in the future. They are suggesting to instead use Func objects. And there is one for extracting a month without using a painful Case statement.

from django.db.models.functions import ExtractMonth
Link.objects.all().annotate(pub_date_month=ExtractMonth('pub_date'))

Django annotate groupings by month

4 Answers4

Linked