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 know it's been long but in case someone else is looking into it, this is what worked for me

from django.db.models import DurationField, ExpressionWrapper, F, DateTimeField
from django.db.models.functions import Extract

MyModel.objects.annotate(
    duration=ExpressionWrapper(
        F('end_date') - F('start_date'),
        output_field=DurationField()
    )
).annotate(
    duration_in_days=Extract('duration', 'day')
)

Also for my case I was trying to get the difference between current date and a field, I used the following

from django.utils import timezone
today = timezone.now().date()
qs.annotate(
        duration=ExpressionWrapper(
            Cast(today, DateTimeField()) - F('date'),
            output_field=DurationField()
        )
    ).annotate(
        duration_in_days=Extract('duration', 'day')
    )

Answer 2

If you are using MYSQL database, You could do it using Custom DB Function as,

from django.db.models.functions import Func


class TimeStampDiff(Func):
    class PrettyStringFormatting(dict):
        def __missing__(self, key):
            return '%(' + key + ')s'

    def __init__(self, *expressions, **extra):
        unit = extra.pop('unit', 'day')
        self.template = self.template % self.PrettyStringFormatting({"unit": unit})
        super().__init__(*expressions, **extra)

    function = 'TIMESTAMPDIFF'
    template = "%(function)s(%(unit)s, %(expressions)s)"

Usage

from django.db.models import F, IntegerField

booking_queryset = Booking.objects.annotate(
    days=TimeStampDiff(F('start'), F('end'), output_field=IntegerField()))
if booking_queryset.exist():
    print(booking_queryset[0].__dict__)

Answer 3

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

Answer 4

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