CODEWITHSUNDEEP
djangopython-3.x
sarpn
sarpn

Reputation: 108

Finding overlapping dates in a specific range in Django

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().

Upvotes: 3

Views: 2299

Answers (3)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476669

Two intervals (s1, t1) and (s2, t2) do not overal, given t1 < s2, or t2 < s1. This thus means that two resources overlap given t1 ≥ s2 and t2 ≥ s1.

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.

Upvotes: 9

damjad
damjad

Reputation: 1280

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

Upvotes: 2

El hosayn Ait Ali
El hosayn Ait Ali

Reputation: 53

@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

Upvotes: 2

Related Questions