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?

related: https://stackoverflow.com/q/8432926 – djvg Aug 30 '21 at 09:19 — djvg, Aug 30 '21 at 09:19

Oli · Accepted Answer · 2016-07-20T13:45:20.757

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

score 12 · Answer 2 · answered Nov 10 '17 at 11:44

12

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

answered Nov 10 '17 at 11:44

MarcinEl

211
2
6

1

this only works in case the month and year have same value. Otherwise it will fail – rrawat Apr 15 '20 at 23:43
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.. – Mahrus Khomaini Jun 02 '22 at 04:22

score 0 · Answer 3 · answered Aug 10 '18 at 04:06

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

How to annotate a difference of datetime in days

3 Answers3

Linked