Django ORM: Add integer days to a DateField to annotate next_service and filter it (PostgreSQL)

I am trying to annotate a queryset with next_service = last_service + verification_periodicity_in_days and then filter by that date. I am on Django 5.2.6 with PostgreSQL. last_service is a DateField. verification_periodicity lives on a related SubCategory and is the number of days (integer).

Models (minimal):

# main/models.py

class Category(models.Model):
    name = models.CharField(max_length=100)

class SubCategory(models.Model):
    category = models.ForeignKey(Category, on_delete=models.CASCADE, related_name='subcategories')
    name = models.CharField(max_length=100)
    verification_periodicity = models.IntegerField()  # days

class Asset(models.Model):
    sub_category = models.ForeignKey(SubCategory, on_delete=models.PROTECT)
    last_service = models.DateField(null=True, blank=True)

Goal: Compute next_service = last_service + verification_periodicity days in the database, expose it in the API, and support filtering like ?next_date__gte=2025-12-06.

What I tried:

  1. Simple cast and multiply:
from django.db.models import ExpressionWrapper, F, DateField, IntegerField
from django.db.models.functions import Cast

qs = qs.annotate(
    next_service = ExpressionWrapper(
        F('last_service') + Cast(F('sub_category__verification_periodicity'), IntegerField()) * 1,
        output_field=DateField()
    )
)

This does not shift by days and later caused type issues. Filtering by the annotated date also did not work as expected.

  1. Using a Python timedelta:
from datetime import timedelta

qs = qs.annotate(
    next_service = F('last_service') + timedelta(days=1) * F('sub_category__verification_periodicity')
)

This produced a duration in seconds in the serialized output. Example: "next_service": "86400.0" for one day, rather than a proper date. I need a date.

Errors seen along the way:

  • ProgrammingError: operator does not exist: interval * interval when I ended up multiplying two intervals in SQL.
  • "next_service": "86400.0" which suggests I created a duration rather than a date.

Environment:

  • Django 5.2.6
  • Python 3.11.5
  • PostgreSQL (AWS RDS and local)
  • URL example that should work: GET /api/main/assets/?next_date__gte=2025-12-06

Questions:

  1. What is the correct, idiomatic Django ORM expression to compute last_service + verification_periodicity days as a date on PostgreSQL?
  2. How should I define output_field so the annotation is a date, not a duration?
  3. What is the proper way to filter by this annotated date in the same query, for example next_service__gte=<some date>?
Вернуться на верх