Django convert timedelta to integer in annotation

Question

I'm annotating the difference between two dates then aggregating the rounded average. The problem is that the difference between the two dates is a datetime.timedelta so I am getting the error:

django.db.utils.ProgrammingError: cannot cast type interval to numeric

How can can I use the integer days_to_pay.days in the Avg()?

def aggregate_ar_detail(self):
    queryset = self.annotate(
        days_to_pay=Case(
            When(Q(date_paid__isnull=False), then=F('date_paid') - F('date_trans')),
            default=None,
        )
    ).aggregate(
        avg_days=Round(Avg('days_to_pay')),
    )

I've tried specifying the output field on the annotation a models.IntegerField() but it causes:

TypeError: float() argument must be a string or a number, not 'datetime.timedelta'

Do the answers here help? https://stackoverflow.com/questions/25646200/python-convert-timedelta-to-int-in-a-dataframe Honestly I don't know if this is a duplicate so not flagged yet — ggdx, Aug 28 '19 at 11:38
@ggdx same language and same problem but completely different solutions. Not to mention using `.days` (the solution from your link) was included in my question. — bdoubleu, Aug 28 '19 at 11:51

score 5 · Accepted Answer · edited Sep 06 '22 at 15:25

5

Managed to get it working with ExtractDay

from django.db.models.functions import ExtractDay

def aggregate_ar_detail(self):
    queryset = self.annotate(
        days_to_pay=Case(
            When(Q(date_paid__isnull=False), then=F('date_paid') - F('date_trans')),
            default=None,
        )
    ).aggregate(
        avg_days=Round(Avg(ExtractDay('days_to_pay'))),
    )

edited Sep 06 '22 at 15:25

djvg

11,722
5
72
103

answered Aug 28 '19 at 11:54

bdoubleu

5,568
2
20
53

Just ran into a similar problem, when I wanted to convert a duration from seconds to minutes. In order to use the Extract-method, I had to specify an output field though: `self.annotate(duration_minutes=ExpressionWrapper(Max('date_time') - Min('date_time'), output_field=DurationField())`. I didn't run your code, but could it be, the ExpressionWrapper is missing in your answer? (Could of course also be, that the `F()-F()` has a different return value than `Min()-Max()`) – Kim Jun 11 '20 at 15:20

Django convert timedelta to integer in annotation

1 Answers1

Linked