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

I’ve got a simple Model like this:

I have a simple model:

class Order(models.Model):
     created = model.DateTimeField(auto_now_add=True)
     total = models.IntegerField() # monetary value
 

And I want to output a month-by-month breakdown of:

I want to output monthly

• How many sales there were in a month (COUNT)
• How many sales are there in a month?
• The combined value (SUM)
• Combined value (sum)

I’m not sure what the best way to attack this is. I’ve seen some fairly scary-looking extra-select queries but my simple mind is telling me I might be better off just iterating numbers, starting from an arbitrary start year/month and counting up until I reach the current month, throwing out simple queries filtering for that month. More database work – less developer stress!

I’m not sure what the best way to attack is. I’ve seen some really scary superselect queries, but my head tells me it’s better to just iterate over the numbers, starting at an arbitrary starting year/month, working up to the current month, and then throw out a simple query filtering for that month . More database work – less developer stress!

What makes most sense to you? Is there a nice way I can pull back a quick table of data? Or is my dirty method probably the best idea?

What makes the most sense to you? Is there a nice way for me to quickly pull the data table back? Or maybe my dirty way is Best idea?

I’m using Django 1.3. Not sure if they’ve added a nicer way to GROUP_BY recently.

I use Django 1.3. Not sure if they recently added a better method for GROUP_BY.

5 solutions

#1

158

Django 1.10 and above

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 .

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

from django.db.models.functions import TruncMonth
 Sales.objects
     .annotate(mOnth=TruncMonth('timestamp')) # 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

Old version

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

Edit

• Added count
• Increased number
• Added information for django >= 1.10
• Add django > information = 1.10

#2

12

Just a small addition to @tback answer: It didn’t work for me with Django 1.10.6 and postgres. I added order_by() at the end to fix it.

Just a small addition to @tback’s answer: with Django 1.10.6 and postgres it doesn’t work. I added order_by() at the end to fix it.

from django.db.models.functions import TruncMonth
 Sales.objects
     .annotate(mOnth=TruncMonth('timestamp')) # Truncate to month and add to select list
     .values('month') # Group By month
     .annotate(c=Count('id')) # Select the count of the grouping
     .order_by()
 

#3

3

Another approach is to use ExtractMonth. I ran into trouble using TruncMonth due to only one datetime year value being returned. For example, only the months in 2009 were being returned. ExtractMonth fixed this problem perfectly and can be used like below:

Another method is to use Extract Month. I’m having trouble using TruncMonth because only one datetime year value is returned. For example, only a few months of 2009 were returned. ExtractMonth can solve this problem very well, as shown below:

from django.db.models.functions import ExtractMonth
 Sales.objects
     .annotate(mOnth=ExtractMonth('timestamp'))
     .values('month')
     .annotate(count=Count('id'))
     .values('month', 'count')
 

#4

0

Here’s my dirty method. It is dirty.

This is my dirty method. It is dirty.

import datetime, decimal
 from django.db.models import Count, Sum
 from account.models import Order
 d = []

 # arbitrary starting dates
 year=2011
 mOnth= 12

 cyear = datetime.date.today().year
 cmOnth= datetime.date.today().month

 while year <= cyear:
     while (year <cyear and month <= 12) or (year == cyear and month <= cmonth):
         sales = Order.objects.filter(created__year=year, created__mOnth=month).aggregate(Count('total'), Sum('total'))
         d.append({
             'year': year,
             'month': month,
             'sales': sales['total__count'] or 0,
             'value': decimal.Decimal(sales['total__sum'] or 0),
         })
         month += 1
     mOnth= 1
     year += 1
 

There may well be a better way of looping years/months but that’s not really what I care about 🙂

Maybe there is a better way to loop the years/months, but that’s not really what I care about:

#5

-2

By month:

Month:

 Order.objects.filter().extra({'month':"Extract(month from created)"}).values_list('month').annotate(Count('id'))
 

By Year:

Year:

 Order.objects.filter().extra({'year':"Extract(year from created)"}).values_list('year').annotate(Count('id'))
 

By day:

Daytime:

 Order.objects.filter().extra({'day':"Extract(day from created)"}).values_list('day').annotate(Count('id'))
 

Don’t forget to import Count

Don’t forget to import the count

from django.db.models import *
 

For django <1.10

django <1.10