Annotate Date interval and get ratio

Using Django Using the ORM
1

Hi there,

I’ve got a model with fields like :

class Fund(models.Model):

    start_date = models.DateField(null=False, blank=False, verbose_name=_('Start Date'))
    end_date = models.DateField(null=False, blank=False, verbose_name=_('End Date'))
    amount = models.DecimalField(max_digits=12, decimal_places=2, verbose_name=_('Amount'), default=0)
    expense = models.DecimalField(max_digits=12, decimal_places=2, verbose_name=_('Expense'), default=0)

My goal is to annotate with the ratio of the ratio of budget consumption and the ratio of spend time to feed my dashboard, in short script:

(expense/amount) / ( (now-start_date)/(end_date -start_date) )

It would rudimentarily emphasize under and over budget consumption.

My first issue was to divide both ‘interval’ of date :

f=Fund.objects.annotate(ratio=-F('expense')/(F('amount')))
 f=f.annotate(duration=Cast(datetime.now(),fields.DateField()) - F('start_date'))
f=f.annotate(total_duration=F('end_date') - F('start_date'))

then without ExpressionWrapper and Casting I’ve got :
f=f.annotate(duration_quotity=F('duration') / F('total_duration'))

with the error :

django.core.exceptions.FieldError: Cannot infer type of ‘/’ expression involving these types: DurationField, DurationField. You must set output_field.

trying to cast the output :
f=f.annotate(duration_quotity=ExpressionWrapper(F('duration') / F('total_duration'), fields.FloatField()))

django.db.utils.ProgrammingError: ERROR: operator does not exist: interval / interval
LINE 1: …::timestamptz)::date - “fund_fund”. “start_date”)) / (interva…
^
HINT: No operators match the given name and argument types.
You must add explicit type conversions.

So I’m trying to full cast :

f=f.annotate(duration_quotity=ExpressionWrapper(Cast(F('duration'), fields.IntegerField()) / Cast(F('total_duration'), fields.IntegerField()), fields.FloatField()))

django.db.utils.ProgrammingError: ERROR: cannot convert type interval to integer

I’ve tryed to directly convert date into integer, but I’ve got quite the same issue. I’ve tried to cast to other fields type without any success.

I’m a newbe in django framework as well as in python, I should have missed some information/keyword.

I’ve seen:

But I’m not digging to SQL code for the moment, and i’m not sure it could solve my issue

I’ve seen too: Database Functions | Django documentation | Django
But I haven’t found anything to convert a datefield into any sort of number.

2

A PostgreSQL interval is a data type of its own. It defines a duration in time, which can be represented in a number of different ways. (Number of seconds, minutes, hours, days, months, etc)

You would use the EXTRACT function to get the representation of an interval as a specific unit. You’re probably primarily interested in section 9.9.1, but I also recommend that you review Tables 9.32 and 9.33 to understand the data type conversions that occur.

1 Like
3

Many thanks @KenWhitesell !

The ‘epoch’ parameter value for the lookup_name field was not referenced in the django doc, my mistake was to not digging into PostgreSQL syntax!

for records, one of the un-factorized solution was for me :

f=f.annotate(duration=Extract(Now(), 'epoch') -Extract(F('start_date'), 'epoch') )
f=f.annotate(total_duration=Extract(F('end_date'), 'epoch') - Extract(F('start_date'), 'epoch'))
f=f.annotate(duration_quotity=F('duration')/ F('total_duration'))
f=f.annotate(time_ratio=-F('expense') / ((F('amount'))*F('duration_quotity')))

note : I can’t find section 9.32 and 9.33 in current postgres doc, it seems ending at 9.30.
Do I miss smthg?

4

Not sections, table 9.32 and table 9.33.