how to take average of every 10 minutes of a model django

Question

I am using multiple APIs and saving them to the database. I have one model called Station (it has a DateTime field and some other fields) and every API is for one station. These APIs come from devices that measure some variables and they get updated every 3 minutes.

I wrote a background task that calls a saveToDB function and stores them in the database. For example:

1. station A "some variables" 2022/10/1 13:10
2. station B "some variables" 2022/10/1 13:10
3. station A "some variables" 2022/10/1 13:13
4. station B "some variables" 2022/10/1 13:13

Now I need to take an average of every station every 10 minutes, 2 hours, week, month, and year.

There are 30 stations. How can I do this?

Answer 1

If your question is what the django code would look like to make these calculations, your should read up here on aggregation. Jump down to the "values()" section of the page. The code to group by station and calculate the average of all entries would be:

Station.objects.values('station_id').annotate(myAverage=Avg('some_variable'))

This will group all entries by station.

However, you can simplify by using a nested loop to isolate each station and run the average over each 10 minute interval. Not sure exactly what the conditions for which 10 minute intervals you need, but let's say you want each 10 minutes from yesterday. Something like:

from datetime import datetime, timedelta
from django.db.models import Avg
from .models import Station

def test():
    # get yesterday's date at midnight
    yesterday_at_midnight = datetime.today().replace(hour=0, minute=0, second=0, microsecond=0) - timedelta(days=1)

    # add 10 minutes to the yesterday_at_midnight variable
    ten_minutes_after_midnight = yesterday_at_midnight + timedelta(minutes=10)

    # set the start time to yesterday_at_midnight
    start_time = yesterday_at_midnight
    # set the end time to ten_minutes_after_midnight
    end_time = ten_minutes_after_midnight

    # loop over each station in the database
    for s in Station.objects.all():
        # loop over the next 143 10 minute intervals (24 hours - 1 interval)
        for i in range(143):
            # find all the Station objects that fall within the current 10 minute interval
            stations = Station.objects.filter(station_id=s.station_id, created_at__gte=start_time, created_at__lt=end_time).aggregate(Avg('some_variable'))
            # do something with this QuerySet
            print(stations)
            # increment the start and end times by 10 minutes
            start_time += timedelta(minutes=10)
            end_time += timedelta(minutes=10)