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

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.

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()),
    ),
)
Back to Top