Writing Django Func() Expression with multiple parameters and specify the order

Question

I'm using Func() Expressions to use this answer and compute the difference between two dates in business days:

class BusinessDaysBetween(Func):
    """Implementation of a Postgres function to compute the working holidays between two fields."""
    template = """
        (SELECT COUNT(*) FROM generate_series(%(expressions)s, interval '1 day') s(day)
        WHERE EXTRACT(DOW FROM s.day) NOT IN (0, 6))
    """
    arity = 2
    output_field = IntegerField()

However, I am having problems with the formatting of Date and DateTime. So I want to call the functions mentioned in this answer.

My edited code looked like this:

class BusinessDaysBetween(Func):
    """Implementation of a Postgres function to compute the working holidays between two fields."""
    template = """
        (
            SELECT COUNT(*) FROM generate_series(
                TO_CHAR(CAST(%(expressions)s AS DATE), 'YYYY-MM-DD'),
                TO_CHAR(CAST(%(expressions)s[1] AS DATE), 'YYYY-MM-DD'),
                interval '1 day'
            ) s(day)
            WHERE EXTRACT(DOW FROM s.day) NOT IN (0, 6)
        )
    """
    arity = 2
    output_field = IntegerField()

The problem is that I am putting both parameters in the first place, I don't know how to specify the order of the parameters in which they will appear. I already tried:

• With {0} and {1} and it says there is a syntax error.
• %(expressions)s and %(expressions)s[1] and nothing.
• With %s, it's raising the "not enough arguments for format string" error.

Answer 1

Actually, your first variant of the BusinessDaysBetween function works, you just need to do a type conversion of datetime to date using functions.Cast. For example, database queries like this will work:

import datetime  
  
from django.db import models  
from django.db.models.functions import Cast


class BusinessDaysBetween(models.Func):
    """Implementation of a Postgres function to compute the working holidays between two fields."""
    template = """
        (SELECT COUNT(*) FROM generate_series(%(expressions)s, interval '1 day') s(day)
        WHERE EXTRACT(DOW FROM s.day) NOT IN (0, 6))
    """
    arity = 2
    output_field = models.IntegerField()


end_at = datetime.datetime.now(datetime.UTC)

queryset1 = YourModel.objects.annotate(
    business_days_count=BusinessDaysBetween(
        Cast(end_at - datetime.timedelta(days=10), models.DateField()),
        Cast(end_at, models.DateField()),
    ),
)

#  or using `models.F` to reference your model field
queryset2 = YourModel.objects.annotate(
    business_days_count=BusinessDaysBetween(
        Cast(models.F('started_at'), models.DateField()),
        Cast(models.F('finished_at'), models.DateField()),
    ),
)

UPDATED

Here's another option I thought of (it's something similar to your second example), although it's less flexible than the first one, e.g. it won't work with other expressions like models.F, but it will work with python objects like datetime and str. But you have to be careful with this example, here's a quote from the documentation:

The **extra kwargs are key=value pairs that can be interpolated into the template attribute. To avoid an SQL injection vulnerability, extra must not contain untrusted user input as these values are interpolated into the SQL string rather than passed as query parameters, where the database driver would escape them.

You could define your function this way:

class BusinessDaysBetween(models.Func):
    template = """
        (
            SELECT COUNT(*) FROM generate_series(
                CAST('%(from_date)s' AS DATE),
                CAST('%(to_date)s' AS DATE),
                interval '1 day'
            ) s(day)
            WHERE EXTRACT(DOW FROM s.day) NOT IN (0, 6)
        )
    """
    output_field = models.IntegerField()

In this case you need to use keyword arguments rather than positional arguments, for example:

import datetime

end_at = datetime.datetime.now(datetime.UTC)  
queryset = YourModel.objects.annotate(  
    business_days_count=BusinessDaysBetween(  
        from_date=end_at - datetime.timedelta(days=5),  
        to_date=str(end_at),
    )  
)

Both examples should work for you and produce identical results, but 10 times out of 10 I would use the first option because of its reliability and greater flexibility.