How can I rewrite an average value per date time interval MySQL query as a Django QuerySet?

Question

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.

Answer 1

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")
)