Django ORM: timedelta difference in days between 2 dates

Question

I have a database table that represents expenses associated with a given product.

These expenses, given that they're daily, have a from_date (the date in which they started) and to_date (the date in which they ended). to_date can be null, as these expenses might still be going.

Given 2 Python datetimes, start_date and end_date, I need to produce in the ORM the total spent in the period for my_product.

>>> start_date
datetime.datetime(2021, 8, 20, 0, 0)

>>> end_date
datetime.datetime(2021, 9, 21, 0, 0)

In this case, the expected output should be:

(-104 * (days between 08/20 and 08/25)) + (-113 * (days between 08/26 and 09/21)

This is what I've got so far:

(
my_product.income_streams
    .values("product")
    .filter(type=IncomeStream.Types.DAILY_EXPENSE)
    .filter(add_to_commission_basis=True)
    .annotate(period_expenses=Case(
        When(Q(from_date__lte=start_date) & Q(to_date__lte=end_date),
 then=ExpressionWrapper( start_date - F('to_date'), output_field=IntegerField()))
    ), # Other When cases...
)
) # Sum all period_expenses results and you've got the solution

And this is what's giving me problems:

then=ExpressionWrapper( start_date - F('to_date'), output_field=IntegerField())

This expression always returns 0 (please note this is why I'm not even attempting to multiply by value: that'd be the next step).

Apparently start_date - F('to_date') is not the same as "give me the difference in days between these 2 dates".

You'd acomplish this in Python with timedelta. What's the equivalent in the ORM?

I've tried with ExtractDay:

then=ExpressionWrapper( ExtractDay(start_date - F('to_date'))

But I get: django.db.utils.OperationalError: user-defined function raised exception

And also tried with DurationField:

then=ExpressionWrapper(start_date - F('to_date'), output_field=DurationField())

But that also returns zero: datetime.timedelta(0)

user_1307020 · Answer

Casting start_date into a DateTimeField solves the problem, and casting the difference into a DurationField is the next step.

So:

Cast(Cast(start_date, output_field=DateTimeField()) - F('to_date'), output_field=DurationField())

This will work fine on any databse backend, but in order to get the difference in days, you need to wrap it in ExtractDay, which will throw ValueError: Extract requires native DurationField database support. if you use SQLite.

If you're tied to SQLite and cannot use ExtractDay, you can use microseconds, and convert them manually to days by dividing by 86400000000

duration_in_microseconds=ExpressionWrapper(F('to_date') - (Cast(start_date, output_field=DateTimeField())), output_field=IntegerField())

Then

.annotate(duration_in_days=ExpressionWrapper(F('period_duration_microseconds') / 86400000000, output_field=DecimalField())

Django ORM: timedelta difference in days between 2 dates

1 Answers