query to get date overlaping

Question

I have a model named Company_Position_Person that determine each person works in a company with specified position between startDate and endDate:

class Company_Position_Person(models.Model):
    company_position=models.ForeignKey(Company_Position)
    person=models.ForeignKey(Person)
    startDate=models.CharField(max_length=25)
    endDate=models.CharField(max_length=25)

Now I want to find the List of colleagues of a specific person based on date overlapping.I mean find out which persons works at this company at the same time that a specific person works.For example I work in company at startDate=2012-01-01 - endDate=2012-21-12.Another person that works at the same company at startDate=2012-08-01 - endDate=2013-21-12 is a colleague of me at this time. I found out that custome managers can be useful but I don't know how to find out overlapping while running query and how to pass startDate and endDate of specific person to the manager.

http://stackoverflow.com/a/325939/298479 is the "algorithm" you need to check if the two date ranges overlap. — ThiefMaster, Dec 22 '12 at 12:42

dani herrera · Accepted Answer · 2012-12-22T16:17:08.197

They are 4 cases for Overlaping and 2 for no overlaping:

......********.......   Source User
........****.........   Target User Overlaping
....*****............   Target User Overlaping
..........*******....   Target User Overlaping
....************.....   Target User Overlaping

......********.......   Source User
..**.................   Target User Not Overlaping
................**...   Target User Not Overlaping

No overlaping when taget_end_date < source_begin_date or target_begin_date > source_end_date. Then overlaping where:

NOT (taget_end_date < source_begin_date or target_begin_date > source_end_date )

That is the equal than:

   not (taget_end_date < source_begin_date ) 
 and 
   not( target_begin_date > source_end_date )

That equals

 taget_end_date >= source_begin_date  and  target_begin_date <= source_end_date

For your code:

source = Company_Position_Person.objects.get( pk = 12 )
targets = Company_Position_Person.filter( startDate__lte = target.endDate ,
                                          endDate__gte = target.startDate )

score 1 · Answer 2 · answered Dec 22 '12 at 15:01

1

You can try this query:

person = Company_Position_Person.objects.get(pk=...)
persons = Company_Position_Person.objects.exclude(startDate__gt=person.endDate, endDate__lt=person.startDate,pk=person.pk)

answered Dec 22 '12 at 15:01

sneawo

3,543
1
26
31

query to get date overlaping

2 Answers2