Portable date arithmetic query (PostgreSQL & SQLite)

Using Django Using the ORM
1

I have this model:

class Block(models.Model):
    name = models.CharField(max_length=20)
    date_from = models.DateField()
    date_to = models.DateField()

I have a separate date_from and date_to from a different area of the app and I want to query Block and annotate it with the number of days of overlap between Block.date_from and Block.date_to and the given dates.

PostgreSQL Solution

This works in PostgreSQL:

Block.objects.annotate(
    overlap_days=models.functions.Extract(
        models.functions.Least(models.F("date_to"), models.Value(end_date))
        - models.functions.Greatest(models.F("date_from"), models.Value(start_date))
        + timedelta(days=1),
        'days'
    )
)

But when I run it on SQLite I get this error:

Extract requires native DurationField database support.

SQLite Solution

On the other hand, this query on the other hand works in SQLite:

Block.objects.annotate(
    overlap_days=models.functions.Cast(
        models.ExpressionWrapper(
            (
                models.functions.Least(models.F("date_to"), models.Value(end_date))
                - models.functions.Greatest(models.F("date_from"), models.Value(start_date))
            )
            + 1,
            output_field=models.IntegerField(),
        ),
        models.IntegerField(),
    )
)

But I get this error in PostgreSQL:

UndefinedFunction: operator does not exist: interval + integer
LINE 1: ...T("core_block"."date_from", '2024-10-01'::date))) + 1))::int...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Is there a way to write the query in a generic way so it works on both PostgreSQL and SQLite? I need the ORM annotation because I want to filter & sort by it too, so calculating the number of days in Python is a bit less optimal.

2

In case someone is interested, I solved this by not trying to cast the value to a number of days:

Block.objects.annotate(
            overlap_interval=(
                models.functions.Least(models.F("date_to"), models.Value(end_date))
                - models.functions.Greatest(models.F("date_from"), models.Value(start_date))
                + timedelta(days=1)
            )
        )

I can still order by overlap_interval to get for example block with most overlap and once I got my results, I calculated the actual number of days in Python.