How to annotate a difference of datetime in days

Question

I have a Booking model that has start and end datetime fields. I want to know how many days a booking covers. I can do this in Python but I need this value for further annotations.

Here's what I've tried:

In [1]: Booking.objects.annotate(days=F('end')-F('start'))[0].days
Out[1]: datetime.timedelta(16, 50400)

There are a few problems here:

• I want an integer (or other number type I can use in calculations) of days as the output, not a timedelta. Setting output_field doesn't do anything meaningful here.
• My sums are based on datetimes. Subtractions like this, without removing the time could lead to the whole number of days being off.

In Python I would do (end.date() - start.date()).days + 1. How can I do that in-database, preferably through the ORM (eg database functions), but a RawSQL would suffice to get this out the door?

Answer 1

I've written a couple of database functions to cast and truncate the dates to solve both problems under PostgreSQL. The DATE_PART and DATE_TRUNC internal function I'm using are DB-specific ☹

from django.db.models import Func

class DiffDays(Func):
    function = 'DATE_PART'
    template = "%(function)s('day', %(expressions)s)"

class CastDate(Func):
    function = 'date_trunc'
    template = "%(function)s('day', %(expressions)s)"

Then I can:

In [25]: Booking.objects.annotate(days=DiffDays(CastDate(F('end'))-CastDate(F('start'))) + 1)[0].days
Out[25]: 18.0

Answer 2

There is another, easy solution of this problem. You can use:

from django.db.models import F
from django.db.models.functions import ExtractDay

and then:

Booking.objects.annotate(days=(ExtractDay(F('end')-F('start'))+1))[0].days