Django Monthly/quartarly grouping of DateField() data

Question

I've got a django model which contains, among other things, a DateField() attribute:

class Table():
    date = models.DateField()
    value = models.FloatField()

I'm writing a view that groups this data by week, month Quarter and year. I've hardcoded a calculation that gets my monthly value simply enough - by adding up all the values in that month and deviding by how many entries there were - but I feel like there must be a more elegant way of doing this.

What I'm aiming for is something like this:

get_monthly(Table.objects.all())
>>> [123, 412, 123, 534, 234, 423, 312, 412, 123, 534, 234, 423]
get_quarterly(Table.objects.all())
>>> [123, 412, 123, 534]

Where the values in the list are averages of each month.

Can anyone help me?

Answer 1

You can do this using the model's query capabilities. Here's an example for the monthly query:

from django.db.models import Avg
Table.objects.extra(select={'month':"strftime('%m',date)"}).values('month').annotate(Avg('value'))

Where you may want to change strftime('%m',date) with month(date) or any other calculation, depending on your database datetime functionality.