CODEWITHSUNDEEP
pythondjango
Harry
Harry

Reputation: 13329

Getting a count per hour for the past week

I have a model of Photos taken. I am building a heatmap with Javascript of when photos were taken of the last7 days. I am showing it per hour.

My Photo Model has a timestamp DateTime field. How would I make a list like this showing total photos per hour per day for the past week?

The javascript expects an array like this:

const heatmapDataSource = [
  [0, 0, 3],
  [6, 23, 3],
  ....
];

heatmapDataSource[0][0] is the day of the week (Today (Tuesday) 0, last week Wednesday (6) - Today is always 0

heatmapDataSource[0][1] is hour (0 == 24:00 and 23 == 23:00 etc)

heatmapDataSource[0][2] is the count value for that hour

Hope this makes sense!

So far I have put this together as a start to get counting:

    Photo.objects.annotate(
    hour=Trunc('timestamp', 'hour', output_field=DateTimeField())
).values('timestamp__hour', 'timestamp__day').annotate(photos=Count('id'))

Outputs:

<QuerySet [{'timestamp__hour': 10, 'timestamp__day': 10, 'photos': 8}, {'timestamp__hour': 12, 'timestamp__day': 13, 'photos': 1}]>

The Photo Model:

class Photo(models.Model):
    camera = models.ForeignKey(Camera, null=True, on_delete=models.PROTECT)
    image = models.ImageField()
    timestamp = models.DateTimeField(auto_now=True)

UDPATE

This is what I have sofar that is almost there..

import datetime as DT
today = DT.date.today()
week_ago = today - DT.timedelta(days=7)
from django.db.models.functions import ExtractWeekDay


Photo.objects.filter(timestamp__gte=week_ago).annotate(weekday=ExtractWeekDay('timestamp')).annotate(hour=Trunc('timestamp', 'hour', output_field=DateTimeField())).values('timestamp__hour', 'weekday').annotate(photos=Count('id'))

Upvotes: 1

Views: 440

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476584

First we need to make some fixes to the query itself: the __day will have a range of 1-31, which is probably not what we want. We want the week day, so:

from django.db.models import Count
from django.db.models.functions import ExtractHour, ExtractWeekDay

qs = Photo.objects.annotate(
    hour=ExtractHour('timestamp'),
    day=ExtractWeekDay('timestamp')
).values('hour', 'day').annotate(
    photos=Count('id')
).order_by('hour', 'day')

Next we will need to do some post processing:

from datetime import date
dow = date.today().isoweekday() + 1

result = [
    [(row['day'] - dow) % 7, row['hour'], row['photos']]
    for row in qs
]

This will result in an n×3-list, that can then, for example, be transmitted through JSON.

Upvotes: 1

Related Questions