I have the following MySQL query that displays the average value per 10 minute interval:
SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(date_time) / 600) * 600) AS interval_date_time,
AVG(some_value) AS avg_value
FROM djangoapp_somemodel
GROUP BY interval_date_time
ORDER BY interval_date_time;
For example, if I have the following 3 records:
item_id | date_time | some_value |
---|---|---|
1 | 2021-11-29 00:11:01 | 10 |
2 | 2021-11-29 00:16:15 | 20 |
3 | 2021-11-29 00:24:32 | 25 |
The query will output the following:
interval_date_time | avg_value |
---|---|
2021-11-29 00:10:00 | 15 |
2021-11-29 00:20:00 | 25 |
I suspect the query isn't that efficient but I want to get the same output using a Django QuerySet.
For reference, my Django model looks like this:
class SomeModel(models.Model):
item_id = models.AutoField(primary_key=True)
some_value = models.FloatField()
date_time = models.DateTimeField(auto_now=True)
Here's my current QuerySet:
(SomeModel.objects
.annotate(interval_date_time=F("date_time"))
.values("interval_date_time")
.annotate(avg_value=Avg("some_value"))
.order_by("interval_date_time")
)
I believe I need to make changes to the first annotate method call. Any help would be appreciated as I am quite new to Django.
Might be easiest to just use RawSQL
here if you know you'll always be working with MySQL.
(
SomeModel.objects.annotate(interval_date_time=RawSQL("FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(date_time) / 600) * 600)", ()))
.values("interval_date_time")
.annotate(avg_value=Avg("some_value"))
.order_by("interval_date_time")
)