Django Getting Month From Date For Aggregation

I am trying to get a sum per month response in a DJango ORM query call: models.Subscription.objects.all().extra(select={'Month': 'EXTRACT(Month FROM Modified)'}).annotate(count_ite

Solution 1:

Did you try looking at your schema? I think you just got your column name wrong.

Assuming you are using Djnago>=1.8 you can also avoid the whole issue by writing a Func like this one:

class ExtractMonth(Func):
    template = "EXTRACT(MONTH FROM %(expressions)s)"

    def __init__(self, *expressions, **extra):
        extra['output_field'] = SmallIntegerField()
        super().__init__(*expressions, **extra)
       

and using it like this:

Subscription.objects.annotate(Month=ExtractMonth('Modified'))\
                .values('Month').annotate(Count('Month'))

This way 'Modified' is the name of the field on your model and Django resolves the column name for you.

Update

There is a more generic solution proposed in this 1.9 ticket.