Django group by hour

Question

I have the following model in Django.

class StoreVideoEventSummary(models.Model):
    Customer = models.ForeignKey(GlobalCustomerDirectory, null=True, db_column='CustomerID', blank=True, db_index=True)
    Store = models.ForeignKey(Store, null=True, db_column='StoreID', blank=True, related_name="VideoEventSummary")
    Timestamp = models.DateTimeField(null=True, blank=True, db_index=True)
    PeopleCount = models.IntegerField(null=True, blank=True)

I would like to find out the number of people entering the store each hour.

To achieve this, I'm trying to group the rows by the hour on Timestamp and sum the PeopleCount column.

store_count_events = StoreVideoEventSummary.objects.filter(Timestamp__range=(start_time, end_time),
                                                       Customer__id=customer_id,
                                                       Store__StoreName=store)\
        .order_by("Timestamp")\
        .extra({
            "hour": "date_part(\'hour\', \"Timestamp\")"
        }).annotate(TotalPeople=Sum("PeopleCount"))

This doesn't seem to group the results by the hour, it merely adds a new column TotalPeople which has the same value as PeopleCount to each row in the query set.

Answer 1

just break it into two steps

import itertools
from datetime import datetime


# ...

def date_hour(timestamp):
    return datetime.fromtimestamp(timestamp).strftime("%x %H")


objs = StoreVideoEventSummary.objects.filter(
    Timestamp__range=(start_time, end_time),
    Customer__id=customer_id,
    Store__StoreName=store
).order_by("Timestamp")

groups = itertools.groupby(objs, lambda x: date_hour(x.Timestamp))

# since groups is an iterator and not a list you have not yet traversed the list
for group, matches in groups:  # now you are traversing the list ...
    print(group, "TTL:", sum(1 for _ in matches))

This allows you to group by several distinct criteria

Of you just want the hour regardless of date just change date_hour

def date_hour(timestamp):
   return datetime.fromtimestamp(timestamp).strftime("%H")

If you wanted to group by day of the week you just use

def date_day_of_week(timestamp):
   return datetime.fromtimestamp(timestamp).strftime("%w %H")

And update itertools.groupby's lambda to use date_day_of_week.

Answer 2

Building off your original code, could you try:

store_count_events = StoreVideoEventSummary.objects.filter(Timestamp__range=(start_time, end_time), Customer__id=customer_id, Store__StoreName=store)\
    .extra({
        "hour": "date_part(\'hour\', \"Timestamp\")"
    })\
    .values("hour")\
    .group_by("hour")\
    .annotate(TotalPeople=Sum("PeopleCount"))