Count of records created in each hour interval starting from midnight to present time

Question

I need to retrieve count of records created in each hour duration from midnight to present time. To do so the model contain a created_at timestamp field which will record the created time. The model definition is given bellow.

class Token(models.Model):
    customer_name = models.CharField(max_length=50)
    created_at = models.DateTimeField(auto_now_add=True)
    remarks = models.TextField(null=True,blank=True)
    modified_at = models.DateTimeField(auto_now =True)

The result am looking for like the following in SQL output

Intervel  | count|
-----------------|       
01:00 AM  |   0  |  # 00:00 to 01:00 AM 
02:00 AM  |   0  |
   .......
10:00 AM  |  20  |
11:00 AM  |   5  |
  ........
03:00 PM  |   5  |
------------------

I need to get model query that can do the above operation at the database end without loading all the records created in the day since from midnight to now then getting the count by running a forloop in the application. Kindly Help me in this..

Answer 1

You can first extract the items with the count, but that will not be sufficient, since there can be hours where there is no activity at all, and a database works under the "closed world assumption".

We thus query, with:

from django.db.models import Count
from django.db.models.functions import ExtractHour
from django.utils.timezone import now

data = (
    Tokent.objects.filter(
        created_at__gte=now().replace(hour=0, minute=0, second=0, microsecond=0)
    )
    .values(hour=ExtractHour('created_at'))
    .annotate(number=Count('pk'))
    .order_by('hour')
)
data = {data['hour'] + 1: data['count']}
for h in range(1, 25):
    data.setdefault(h, 0)

Here we thus first query for data, then post-process this to a dictionary, and finally set values for the hours in the range 1-24 that have no data.

Answer 2

You need to use values and annotate for this.

from django.db.models import Count 
from your_models import Token 
from django.utils import timezone

# Get records for today.
records_today = Token.objects.filter(created_at__date=timezone.now().date()) 

# Group the records by the hour, then annotate will work on that group. 
per_hour_count = record_today.values("created_at__hour").annotate(count=Count("pk")).order_by("created_at")