Grouping by week, and padding out 'missing' weeks

Question

In my Django model, I've got a very simple model which represents a single occurrence of an event (such as a server alert occurring):

class EventOccurrence:
    event = models.ForeignKey(Event)
    time = models.DateTimeField()

My end goal is to produce a table or graph that shows how many times an event occurred over the past n weeks.

So my question has two parts:

• How can I group_by the week of the time field?
• How can I "pad out" the result of this group_by to add a zero-value for any missing weeks?

For example, for the second part, I'd like transform a result like this:

| week | count |                   | week | count |
| 2    | 3     |                   | 2    | 3     |
| 3    | 5     |   —— becomes —>   | 3    | 5     |
| 5    | 1     |                   | 4    | 0     |
                                   | 5    | 1     |

What's the best way to do this in Django? General Python solutions are also OK.

Answer 1

Django's DateField as well as datetime doesn't support week attribute. To fetch everything in one query you need to do:

from django.db import connection

cursor = connection.cursor()
cursor.execute(" SELECT WEEK(`time`) AS 'week', COUNT(*) AS 'count' FROM %s GROUP BY WEEK(`time`) ORDER BY WEEK(`time`)" % EventOccurrence._meta.db_table, [])

data = []
results = cursor.fetchall()
for i, row in enumerate(results[:-1]):
    data.append(row)

    week = row[0] + 1
    next_week = results[i+1][0]
    while week < next_week:
        data.append( (week, 0) )
        week += 1
data.append( results[-1] )

print data