pythonmysqldjangodjango-modelsdjango-mysql

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


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.


Solution

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