How to calculate number of remaining monthly recurring dates between now and a date in the future in Django with Postgres

Question

I have a Django application with Django Rest Framework that is storing records in Postgres. A Vehicle model has an end_date DateField that represents the final payment date of a finance agreement, and a monthly_payment FloatField that represents the payment amount. The finance payment is made monthly, on the same day of the month as the final payment (e.g. if end_date is 25/01/2020, a payment is made on the 25th of every month between now and 25/01/2020 inclusive.)

I have a ListVehicle ListCreateAPIView that returns a paginated list of vehicle records. I am using a custom PageNumberPagination class to return a data object alongside the results array that is populated by aggregating some of the fields in the Vehicle model. I want to include a field in this data object that contains the total remaining amount left to pay on all of the Vehicle entities in the database.

I have tried using @property fields in the model that calculate the total remaining amount for each Vehicle, but you can't aggregate over calculated properties (at least not with queryset.aggregate), so the following solution did not work:

    @property
    def remaining_balance(self):
        return max(self.remaining_monthly_payments * self.monthly_payment, 0)

    @property
    def remaining_monthly_payments(self):
        now = datetime.datetime.now()
        end = self.end_date

        months = (end.year - now.year) * 12
        months -= now.month + 1
        months += end.month

        today = now.day
        final_day = end.day

        if today < final_day:
            months += 1

        return months

I have also tried using ExpressionWrappers in the pagination class to first annotate each Vehicle with a time_remaining field and then annotated to have a remaining_balance field calculated by extracting the month from time_remaining and multiplying it by monthly_payment. Then remaining_balance is aggregated.

class VehicleFinancePagination(pagination.PageNumberPagination):
    def paginate_queryset(self, queryset, request, view=None):
        duration = ExpressionWrapper(F('end_date') - Now(), output_field=fields.DurationField())
        queryset = queryset.annotate(duration=duration)
        queryset = queryset.annotate(remaining_balance=ExpressionWrapper(ExtractMonth('duration') * F('monthly_payment'), output_field=FloatField()))

        self.total_remaining_balance = queryset.aggregate(total_remaining_balance=Sum('remaining_balance'))[
            "total_remaining_balance"]
        return super(VehicleFinancePagination, self).paginate_queryset(queryset, request, view)

    def get_paginated_response(self, data):
        paginated_response = super(VehicleFinancePagination, self).get_paginated_response(data)
        paginated_response.data['total_remaining_balance'] = self.total_remaining_balance

        return paginated_response

I have tried several combinations of annotations in this style. (including doing the entire calculation in one annotation). This returns 0 every time. I get a value if I use ExtractDay instead of ExtractMonth, so I think the problem here is that ExtractMonth gets the month of a year from a DateField, but not the number of full months in a DurationField like I'd hoped, although this answer would suggest otherwise

Another thing that doesn't work is storing the remaining balance/remaining months with the Vehicle when it is saved. As soon as a monthly payment date passes, a Vehicle will be out of date until the next time it is saved and as such the aggregated totals will be incorrect.

Have I got the wrong approach? Is it possible to achieve what I'm trying to do with a custom database query, and if so would it be the best way?

Using PostgreSQL 10.10, Django 2.2, DRF 3.9.4 and Python 3.6

EDIT: Testcase for completeness

    @mock.patch('workshop.models.vehicle.datetime')
    def test_remaining_balance(self, mocked_datetime):
        mocked_datetime.datetime.now.return_value = datetime.datetime(2019, 11, 7, 1, 2, 3)

        Vehicle.objects.create(registration="TS01 TST", monthly_payment=500, end_date=datetime.date(2020, 3, 1)) #2000
        Vehicle.objects.create(registration="TS02 TST", monthly_payment=250, end_date=datetime.date(2020, 3, 25)) #1250
        Vehicle.objects.create(registration="TS03 TST", monthly_payment=400, end_date=datetime.date(2020, 5, 1)) #2400
        Vehicle.objects.create(registration="TS04 TST", monthly_payment=300, end_date=datetime.date(2020, 5, 25)) #2100
        Vehicle.objects.create(registration="TS03 TST", monthly_payment=400, end_date=datetime.date(2018, 5, 1)) #0
        Vehicle.objects.create(registration="TS04 TST", monthly_payment=300, end_date=datetime.date(2018, 5, 25)) #0

        url = reverse('vehicles')
        response = self.client.get(url)

        self.assertEqual(response.status_code, status.HTTP_200_OK)
        self.assertEqual(response.data['total_remaining_balance'],  7750)

Answer 1

The following should give you the annotation that you want

The PostgreSQL age function will give you an interval from which we can get the number of months. We can define a custom DB function:

class Age(Func):
    function = 'AGE'

Then we can chain some annotations to calculate the number of months. The interval will be something like 1 year 2 mon 3 days 00:00:00 and ExtractMonth will return 2 for this example so we also need to extract the year and multiply it by 12:

queryset.annotate(
    diff=Age(F('end_date'), datetime.date.today())
).annotate(
    months=ExtractYear('diff') * 12 + ExtractMonth('diff')
)

Then we can chain a further annotation where we calculate the remaining balance using the previous annotations. We have to use ExpressionWrapper and cast to a float as months and monthly_payment are different types:

queryset.annotate(
    diff=Age(F('end_date'), datetime.date.today())
).annotate(
    months=ExtractYear('diff') * 12 + ExtractMonth('diff')
).annotate(
    remaining_balance=ExpressionWrapper(F('months') * F('monthly_payment'), output_field=models.FloatField())
)