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?

Traject

related: https://mcmap.net/q/590337/-aggregate-difference-between-datetime-fields-in-django – Gilberte

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

Traject

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

Onwards

this only works in case the month and year have same value. Otherwise it will fail – Koy

Hi @rawat I have tested with different months and years, it works correctly, does not fail. I'm using django 3.2. Please confirm, thanks.. – Beret

@Onwards great answer. It works even when year and month are different, confirming – Flump

The results are probably not uniform across all available database options. For example ExtractDay will throw an error if the DB doesn't support native duration/interval fields (eg. sqlite). – Possessive

This does not work the way you think. It gets the day value (2022-01-02 gives 2 same as 2033-03-02) so If the dates are in the same month and year, it will return the correct value. Otherwise, you get a wrong value. – Courtneycourtrai

Answer 3

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')
    )

Assignat

Interesting idea. The potential problem with duration is it does not count calendar dates, just 24h periods. It really depends on the use-case to know if that matters. – Traject

Answer 4

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__)

Ayurveda