Finding overlapping dates in a specific range in Django

Question

I have an issue with finding an overlapping date range in Django. I have two models, reunion, which has a range of dates:

class reunion(models.Model):        
    resource = models.ForeignKey(resource, on_delete=models.CASCADE)
    start = models.DateTimeField()
    end = models.DateTimeField()
    title = models.CharField(max_length=100)

And the resource model:

class resource (models.Model):
    site = models.ForeignKey(site, on_delete=models.CASCADE)
    name = models.CharField(max_length=60)    
    def isAvaible(self, endDate, initialDate):
        try:
            self.reunion_set.get(Q(start__lt=endDate) | Q(end__gt=initialDate))  
            return False
        except:
            return True

When I need to make a new reunion with a specific range of dates, I need to find a non-overlapping resource so I use this method:

def getAvaibleAccount(initialDate, endDate):
    avaibleResources = resource.objects.all()

    for avaibleResource in avaibleResources:
        if avaibleResource.isAvaible(initialDate,endDate):
            return avaibleResource           
    return None

But my code says that the date range: (12/30/2019 11:00 - 12/30/2019 12:00) overlaps with (12/31/2019 11:30 - 12/31/2019 12:30) as if just comparing time and not the date. I've been searching a lot and I'm not having any luck.

Where is my error?

I'm getting the dates as strings and parsing them with dateutil.parser.parse().

The `.get(..)` will raise an error as well if there are two (or more) `resource`s for that range. — Willem Van Onsem, Dec 30 '19 at 16:15
Does this answer your question? [Determine Whether Two Date Ranges Overlap](https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) — dirkgroten, Dec 30 '19 at 16:22

Willem Van Onsem · Answer 1 · 2019-12-30T16:25:44.620

7

Two intervals (s₁, t₁) and (s₂, t₂) do not overal, given t₁ < s₂, or t₂ < s₁. This thus means that two resources overlap given t₁ ≥ s₂ and t₂ ≥ s₁.

This thus means that you should implement a check like:

def isAvaible(self, endDate, initialDate):
    return not self.reunion_set.filter(end__gte=intialDate, start__lte=endDate).exists()

Note that you should not use .get(..) [Django-doc] here, since this will raise an error when there are no records, or when there are multiple records (two or more). You here can make use of .exists() [Django-doc] to find out if such record exists.

edited Dec 30 '19 at 16:25

answered Dec 30 '19 at 16:21

Willem Van Onsem

443,496
30
428
555

It should be `start__lte=endDate` (s1 <= t2) – dirkgroten Dec 30 '19 at 16:25
@dirkgroten: aargghh... yes, too much copy-pasting. Thanks. – Willem Van Onsem Dec 30 '19 at 16:26

damjad · Answer 2 · 2022-07-23T12:35:16.060

The answer of Willem will work for most of the cases. However, when we have an invalid interval i.e. start_date is after end_date, it won't work.

I have made a generic function by referencing a similar question.

from django.db.models import Q


def create_query_for_finding_overlapping_intervals(start_date_column, end_date_column_name, start_dt, end_dt,
                                                   closed_interval=True):
    """
    Creates a query for finding intervals in the Django model which overlap the [start_date, end_date] closed interval.
    It also takes care of the invalid interval case when start date > end date for both stored ones and the input ones.

    :param start_date_column: name of start date column in the model
    :param end_date_column_name: name of end date column in the model
    :param start_dt: start date of the interval to be checked
    :param end_dt: end date of the interval to be checked
    :param closed_interval: closed interval = True means intervals are of the form [start, end],
     otherwise intervals are of the form [start, end). Where ")" means end-value is included and ")" end-value is not
    included.
    :return:
    """

    q_start_dt__gt = f'{start_date_column}__gt'
    q_start_dt__gte = f'{start_date_column}__gte'
    q_start_dt__lt = f'{start_date_column}__lt'
    q_start_dt__lte = f'{start_date_column}__lte'
    q_end_dt__gt = f'{end_date_column_name}__gt'
    q_end_dt__gte = f'{end_date_column_name}__gte'
    q_end_dt__lt = f'{end_date_column_name}__lt'
    q_end_dt__lte = f'{end_date_column_name}__lte'

    q_is_contained = Q(**{q_start_dt__gte: start_dt}) & Q(**{q_end_dt__lte: end_dt})
    q_contains = Q(**{q_start_dt__lte: start_dt}) & Q(**{q_end_dt__gte: end_dt})
    q_slides_before = Q(**{q_start_dt__lt: start_dt}) & Q(**{q_end_dt__lt: end_dt})
    q_slides_after = Q(**{q_start_dt__gt: start_dt}) & Q(**{q_end_dt__gt: end_dt})
    if closed_interval:
        q_slides_before = q_slides_before & Q(**{q_end_dt__gte: start_dt})
        q_slides_after = q_slides_after & Q(**{q_start_dt__lte: end_dt})
    else:
        q_slides_before = q_slides_before & Q(**{q_end_dt__gt: start_dt})
        q_slides_after = q_slides_after & Q(**{q_start_dt__lt: end_dt})

    return q_contains | q_is_contained | q_slides_before | q_slides_after

Usage:

def isAvaible(self, endDate, initialDate):
    query_obj = create_query_for_finding_overlapping_intervals('start', 'end', initialDate, endDate)
    return not self.reunion_set.filter(query_obj).exists()

score 1 · Answer 3 · answered Jul 23 '22 at 00:24

1

@damjad solution worked for me but with a little modification at the last line which should be like this:

return q_contains | q_is_contained | q_slides_before | q_slides_after

answered Jul 23 '22 at 00:24

El hosayn Ait Ali

43
1
6

Finding overlapping dates in a specific range in Django

3 Answers3