Django ORM to average data in 15 minutes interval

Using Django
1

Hi everyone. I have been scraping a data for every 30 seconds and storing it in this model.

class Frequency(models.Model):
	"""Store the frequency  scraped"""
	timestamp = models.DateTimeField()
	frequency = models.DecimalField(max_digits=5, decimal_places=2)

Now i have been given a task that for every 15 minutes of a day i have to average out the results and group into something like 08:15-08:30, 08:30-08:45 … 23:45-24:00.

What i thought is to use two loops. The outer one will loop in the hours in a day and the inner one will loop in (00, 15, 30, 45) and then alter todays datetime.now() and filter it.

Is there any better way or this is fine??

2

In general terms, you want to take an expression along the lines of timestamp (as an epoch time value) // 900 * 900 to identify which “bin” the value needs to be put. (That expression rounds the epoch time down to the nearest 15 minute increment - 900 = 15 * 60)

I believe that whether you need to do this in your code or if you can do it directly in a query somewhat depends upon which database you’re using. I think this may be one of those cases where the limitations of the database may affect how you code this, because the functions available with SQL vary. (Call this a bit of a guess. I could be way wrong.)

Ken

3

Hey Ken, thanks for the suggestion. I am using Postgresql and the approach that i took is first i filter out all the data that is present for a given date. Then i create two loops. The outer one iterates in range(24) and the inner one in (0, 15, 30, 45) and then filter out the data which falls under the categories 00:00-00:15, 00:15-00:30, … 23:30-23:45, 23:45-00:00 and average it. It works and gives me 96 results. I just wanted to know if there is some other and efficient way to do it.

4

Yes, my suggestion above allows you to do it in one loop instead of two nested loops by creating bins for the day of 15 minute increments rather than breaking it apart twice.
I’ll have to look to see how to best create those bins with PostgreSQL.