0

I wanted to calculate business days in Django annotate. For example, if an event was generated 7 days back, and I wanted to know how many business days had passed. As per the example, 7 days includes [Monday - Sunday], and I only wanted to include [Monday - Friday], which means 5 business days. I've done this logic via some pythonic hack, but I wanted to do this in Django annotate() method. So I can filter the results based on business days.

Here is an example of what I've done so far:

table_values = Table.objects.all().annotate(issue_severity=datetime.utcnow() - F("event__created"))

for table in table_values:
    date = datetime.now(timezone.utc) - table.issue_severity
    dates = (date + timedelta(x + 1) for x in range(table.issue_severity.days))
    table.business_days = sum(day.weekday() < 5 for day in dates)
Muhammad Hammad
  • 183
  • 1
  • 11
  • 3
    I don't think there's a simple way to do it inside an annotate() statement. In order to do that, the SQL server would need to support finding how many business days there are between two dates. You can sorta write your own function for doing this, but it's pretty annoying - see [mysql](https://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates) and [postgres](https://dba.stackexchange.com/questions/207701/count-business-days-between-2-dates-in-postgresql) examples. – Nick ODell Nov 09 '22 at 02:35
  • @NickODell, is there any method to write a custom function in Django and use it inside the ORM query? btw, I'm using the postgres – Muhammad Hammad Nov 09 '22 at 02:42
  • 2
    You can't write a custom function *in Django* and use it in annotate(). annotate() runs on the SQL server, so anything it calls must be written in SQL, not Python. But you could define [a custom Postgres function](https://dba.stackexchange.com/a/247130/199066) and call your Postgres function from annotate() using a [Func() expression](https://docs.djangoproject.com/en/4.1/ref/models/expressions/#func-expressions). – Nick ODell Nov 09 '22 at 02:47
  • @NickODell, Gotacha! – Muhammad Hammad Nov 09 '22 at 03:00

0 Answers0