How to group objects by hour with django querysets?

Question

Let's say I have a model that looks like this:

class Action(models.Model):
    timestamp = models.DateTimeField(default=timezone.now)
    # ...

How do I, in a view,

• Get All actions objects
  • Action.objects

• Group them into 24 brackets (using QuerySet properties, probably a group ?), like so:

  { 0: [Action1, Action2, Action3], # timestamp between 00:00 and 00:59, any day
    1: [Action4], # timestamp between 01:00 and 01:59, any day
    # ...
    23: [ActionN] # timestamp between 23:00 and 23:59, any day
  }
  

Basically, I want to have a graph showing how many Actions were done , with a resolution of an hour.

Answer 1

You can leverage the ExtractHour function to get hour from your datetime field, and then group your records into a dict.

Sample code for your reference:

from collections import defaultdict
from django.db.models.functions import ExtractHour

# create a new field to extract hour from timestamp field
actions = Action.objects.annotate(hour=ExtractHour('created'))

# create a default dict to insert a list if key is not exist
results = defaultdict(lambda: [])
for row in actions:
    results[row.hour].append(row)

NOTE:

If you are using mysql database, pls remember to load timezone info before using Extract functions

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql