Django: Count objects by month

user_13520223 0
Viewed 778

I studied the Django docu and tried many examples but it still doesn't work, though it should be very easy, actually.

This is my model:

class Dummy(models.Model):
    timestamp = models.DateTimeField()

I have some sample objects which I am able to list via the shell like that:

% python manage.py shell
Python 3.7.7 (default, Mar 10 2020, 15:43:33) 
[Clang 11.0.0 (clang-1100.0.33.17)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from conv.models import Dummy
>>> dummy = Dummy.objects.order_by('timestamp')
>>> for d in dummy:
...     print(d.id, d.timestamp)

6 2019-05-01 07:00:06+00:00
4 2020-04-01 22:00:00+00:00
5 2020-04-15 04:00:00+00:00
1 2020-05-17 09:42:00+00:00
2 2020-05-18 09:42:15+00:00
3 2020-05-19 09:42:21+00:00
>>>

Now I want to count the objects by year / month. Filtering by year only works:

>>> Dummy.objects.filter(timestamp__year='2020').count()
5

But filtering by month doesn't work:

>>> Dummy.objects.filter(timestamp__month='5').count()
0

Actually the result should be 4. What's wrong?

django
2 Answers

You can make use of a TruncMonth expression [Django-doc] to obtain a queryset of dictionaries where month is a datetime object that always specifies the first of the month, and count will contain the number of items for that given month:

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

Dummy.objects.values(
    month=TruncMonth('timestamp')
).annotate(
    count=Count('pk')
).order_by('month')

Or for a given month, you can use ExtractMonth:

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

Dummy.objects.values(
    month=ExtractMonth('timestamp'),
    year=ExtractMonth('timestamp')
).filter(
    year=2020,
    month=5
).count()
user_675790

There was some trouble with the MySQL-DB I was using very similar to this topic. When I switched back to SQLite I got the correct result:

>>> Dummy.objects.filter(timestamp__month='5').count()
4

Now I also can combine filtering for year and month like that:

>>> Dummy.objects.filter(timestamp__year='2020', timestamp__month='5').count()
3

Thanks to the generous help of Willem Van Onsem I see things clearer now and learned a lot about TruncMonth and ExtractMonth which seems to be very useful tools too.

user_135202230
Related