Django Group Entries by Month

Question

I'm simply trying to group entries in my Data.active_objects that occur in the same month and year. I would like if entries sharing the same month and year would be annotated with the number of occurrences in Data.active_objects

Data.active_objects has a few entries with the same month values in 'ts_create'

What I've tried so far is

test = Data.active_objects.annotate(ts_create__count=Count(TruncMonth('ts_create), distinct=True)

However, this does not produce results that I would expect as every entry in Data.active_objects has an annotated value 'ts_create__count' of 1 even when the entries occur in the same month

Answer 1

You can use TruncMonth ^[Django-doc] to truncate the ts_create to the start of a month, and then annotate with the number of items with:

from django.db.models import Count
from django.db.models.functions import TruncMonth

Data.objects.values(
    month=TruncMonth('ts_create')
).annotate(
    count=Count('pk')
).order_by('month')

This will produce a QuerySet of dictionaries with month and count as keys, like:

<QuerySet [
    {'month': date(2022, 1, 1), 'count': 14},
    {'month': date(2022, 2, 1), 'count': 25},
    {'month': date(2022, 3, 1), 'count': 13},
    {'month': date(2022, 4, 1), 'count': 2}
]>

Answer 2

Try this:

from django.db.models.functions import ExtractMonth, ExtractYear

test = Data.active_objects.annotate(year=ExtractYear('ts_create'),
                                month = ExtractMonth('ts_create')).values('year','month').annotate(total=Count('id'))