CODEWITHSUNDEEP
pythondjangopostgresqldjango-orm
Javad
Javad

Reputation: 2098

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?

Upvotes: 5

Views: 672

Answers (2)

Iain Shelvington
Iain Shelvington

Reputation: 32294

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

Upvotes: 1

Sergey Pugach
Sergey Pugach

Reputation: 5669

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.

Upvotes: 1

Related Questions