Grouping Django model entries by day using its dat

Question

I'm working with an Article like model that has a DateTimeField(auto_now_add=True) to capture the publication date (pub_date). This looks something like the following:

class Article(models.Model):
    text = models.TextField()
    pub_date = models.DateTimeField(auto_now_add=True)

I want to do a query that counts how many article posts or entries have been added per day. In other words, I want to query the entries and group them by day (and eventually month, hour, second, etc.). This would look something like the following in the SQLite shell:

select pub_date, count(id) from "myapp_article"
where id = 1
group by strftime("%d", pub_date)
;

Which returns something like:

2012-03-07 18:08:57.456761|5
2012-03-08 18:08:57.456761|9
2012-03-09 18:08:57.456761|1

I can't seem to figure out how to get that result from a Django QuerySet. I am aware of how to get a similar result using itertools.groupby, but that isn't possible in this situation (explanation to follow).

The end result of this query will be used in a graph showing the number of posts per day. I'm attempting to use the Django Chartit package to achieve this goal. Chartit puts a constraint on the data source (DataPool). The source must be a Model, Manager, or QuerySet, so using itertools.groupby is not an option as far as I can tell.

So the question is... How do I group or aggregate the entries by day and end up with a QuerySet object?

Answer 1

Create an extra field that only store date data(not time) and annotate with Count:

Article.objects.extra({'published':"date(pub_date)"}).values('published').annotate(count=Count('id'))

Result will be:

published,count
2012-03-07,5
2012-03-08,9
2012-03-09,1