How to populate a list with items.count() from a queryset sorted by a datetime field

I had a hard time formulating the title, so please edit it if you have a better one :)

I'm trying to display some statistics using the pygooglechart. And I am using Django to get the database items out of the database.

The database items has a datetime field wich i want to "sort on". What i really want is to populate a list like this.

data = [10, 12, 51, 50]

Where each list item is the number(count) of database items within an hour. So lets say i do a query that gets all items in the last 72 hours, i want to collect the count of each hour into a list item. Anybody have a good way to do this?

Solution

Assuming you're running Django 1.1 or a fairly recent checkout, you can use the new aggregation features. Something like:

counts = MyModel.objects.values('datettimefield').annotate(Count('datettimefield'))

This actually gets you a list of dictionaries:

[{'datetimefield':<date1>, 'datettimefield__count':<count1>},
 {'datetimefield':<date2>, 'datettimefield__count':<count2>}, ...]

but it should be fairly easy to write a list comprehension to get the format you want.

Edited after comment: If you're on 1.0.2, the most efficient thing to do is to fall back to raw SQL.

cursor = connection.cursor()
cursor.execute(
     "SELECT COUNT(0) FROM `mymodel_table` "
     "GROUP BY `mydatetimefield`;"
)
counts = cursor.fetchall()