Django: Group by date (day, month, year)

by

Django 1.10 and above

Django documentation lists extra as deprecated soon. (Thanks for pointing that out @seddonym, @Lucas03). I opened a ticket and this is the solution that jarshwah provided.

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

Sales.objects
    .annotate(month=TruncMonth('created'))  # Truncate to month and add to select list
    .values('month')                          # Group By month
    .annotate(c=Count('id'))                  # Select the count of the grouping
    .values('month', 'c')                     # (might be redundant, haven't tested) select month and count

Older versions

from django.db import connection
from django.db.models import Sum, Count

truncate_date = connection.ops.date_trunc_sql('month', 'created')
qs = Order.objects.extra({'month':truncate_date})
report = qs.values('month').annotate(Sum('total'), Count('pk')).order_by('month')

Edits

  • Added count
  • Added information for django >= 1.10

More Related Contents:

  1. Django 1.11 Annotating a Subquery Aggregate
  2. How to group by AND aggregate with Django
  3. Hi I am new to Django Framework.getting error :No Module Named
  4. When saving, how can you check if a field has changed?
  5. How do I do an OR filter in a Django query?
  6. How to add url parameters to Django template url tag?
  7. Django admin: how to sort by one of the custom list_display fields that has no database field
  8. Override default queryset in Django admin
  9. Need to convert a string to int in a django template
  10. Django directory structure?
  11. Django migrate –fake and –fake-initial explained
  12. Creating a model and related models with Inline formsets
  13. How to handle request.GET with multiple variables for the same parameter in Django
  14. _set in Django for a queryset
  15. Django: How to get current user in admin forms?
  16. Django templates – split string to array
  17. Docker app server ip address 127.0.0.1 difference of 0.0.0.0 ip
  18. django – ordering queryset by a calculated field
  19. How to add data into ManyToMany field?
  20. ImportError: No module named django.core.wsgi Apache + VirtualEnv + AWS + WSGI
  21. django-orm case-insensitive order by
  22. How do you filter a nested serializer in Django Rest Framework?
  23. How to reset the sequence for IDs on PostgreSQL tables
  24. Django The ‘image’ attribute has no file associated with it
  25. Django query filter with variable column
  26. Migrating existing auth.User data to new Django 1.5 custom user model?
  27. Override default Django translations
  28. git push Heroku main – error: src refspec main does not match any
  29. Django / Comet (Push): Least of all evils?
  30. CSRF verification failed. Request aborted. on django

Leave a Comment