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

Question

I've got a simple Model like this:

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:

• How many sales there were in a month (COUNT)
• The 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!

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?

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

Answer 1

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

Answer 2

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.

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()

Answer 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:

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

Answer 4

    metrics = {
        'sales_sum': Sum('total'),
    }
    queryset = Order.objects.values('created__month')
                               .annotate(**metrics)
                               .order_by('created__month')

The queryset is a list of Order, one line per month, combining the sum of sales: sales_sum

@Django 2.1.7

Answer 5

Here's 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 :)

Answer 6

By month:

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

By Year:

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

By day:

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

Don't forget to import Count

from django.db.models import Count

For django < 1.10

Answer 7

Here is how you can group data by arbitrary periods of time:

from django.db.models import F, Sum
from django.db.models.functions import Extract, Cast
period_length = 60*15 # 15 minutes

# Annotate each order with a "period"
qs = Order.objects.annotate(
    timestamp=Cast(Extract('date', 'epoch'), models.IntegerField()),
    period=(F('timestamp') / period_length) * period_length,
)

# Group orders by period & calculate sum of totals for each period
qs.values('period').annotate(total=Sum(field))