Django queryset aggregate by time interval

Question

Hi I am writing a Django view which ouputs data for graphing on the client side (High Charts). The data is climate data with a given parameter recorded once per day.

My query is this:

format = '%Y-%m-%d' 
sd = datetime.datetime.strptime(startdate, format)
ed = datetime.datetime.strptime(enddate, format)

data = Climate.objects.filter(recorded_on__range = (sd, ed)).order_by('recorded_on')

Now, as the range is increased the dataset obviously gets larger and this does not present well on the graph (aside from slowing things down considerably).

Is there an way to group my data as averages in time periods - specifically average for each month or average for each year?

I realize this could be done in SQL as mentioned here: django aggregation to lower resolution using grouping by a date range

But I would like to know if there is a handy way in Django itself.

Or is it perhaps better to modify the db directly and use a script to populate month and year fields from the timestamp?

Any help much appreciated.

Answer 1

Have you tried using django-qsstats-magic (https://github.com/kmike/django-qsstats-magic)?

It makes things very easy for charting, here is a timeseries example from their docs:

from django.contrib.auth.models import User
import datetime, qsstats

qs = User.objects.all()
qss = qsstats.QuerySetStats(qs, 'date_joined')

today = datetime.date.today()
seven_days_ago = today - datetime.timedelta(days=7)

time_series = qss.time_series(seven_days_ago, today)
print 'New users in the last 7 days: %s' % [t[1] for t in time_series]