djangodatetimedjango-annotatedjango-database-functions

How to annotate a difference of datetime in days


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:

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?


Solution

  • 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