Custom TruncFunc in Django ORM

I have a Django model with the following structure:

class BBPerformance(models.Model):
    marketcap_change = models.FloatField(verbose_name="marketcap change", null=True, blank=True)
    bb_change = models.FloatField(verbose_name="bestbuy change", null=True, blank=True)
    created_at = models.DateTimeField(verbose_name="created at", auto_now_add=True)
    updated_at = models.DateTimeField(verbose_name="updated at", auto_now=True)

I would like to have an Avg aggregate function on objects for every 3 days.
for example I write a queryset that do this aggregation for each day or with something like TruncDay function.

queryset = BBPerformance.objects.annotate(day=TruncDay('created_at')).values('day').annotate(marketcap_avg=Avg('marketcap_change'),bb_avg=Avg('bb_change')

How can I have a queryset of the aggregated value with 3-days interval and the index of the second day of that interval?

Solution 1:

I guess it's impossible on DB level (and Trunc is DB level function) as only month, days weeks and so on are supported in Postgres and Oracle.

So what I would suggest is to use TruncDay and then add python code to group those by 3 days.

Solution 2:

The following should work, although it's slightly ugly.

If you get the difference in days between each row's date and the min date you can then take the Mod of this difference to work out how many days you need to shift to get "middle" date. This middle date can then be grouped on using a values query

import datetime
from django.db.models import F, IntegerField, Avg, Min, DurationField, DateField
from django.db.models.functions import Cast, Mod, Extract

BBPerformance.objects.order_by(
    'created_at'
).annotate(
    diff=F('created_at__date') - BBPerformance.objects.aggregate(min=Min('created_at__date'))['min']
).annotate(
    diff_days=Cast(Extract('diff', 'days'), output_field=IntegerField())
).annotate(
    to_shift=Mod('diff_days', 3) - 1
).annotate(
    grouped_date=Cast(F('created_at__date') - Cast(F('to_shift') * datetime.timedelta(days=1), output_field=DurationField()), output_field=DateField())
).order_by(
    'grouped_date'
).values(
    'grouped_date'
).annotate(
    marketcap_avg=Avg('marketcap_change'),
    bb_avg=Avg('bb_change')
)