CODEWITHSUNDEEP
djangopostgresql
Anish
Anish

Reputation: 5058

Pgsql overlap function in Django

I am working on project where I need to pull the date from a table based on startdate and enddate. Here is the query 

SELECT *  FROM billing_lines  WHERE (start_date, end_date) OVERLAPS ('2018-09-15 03:00:00', '2018-09-15 03:30:00');

I know i can use raw() to execute the above query. But my question is , How to convert this into Django ORM query.

Upvotes: 2

Views: 982

Answers (2)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477338

Two ranges [a1, b1) and [a2, b2) do not overlap if a1≥b2, or b1≤a2. We can negate this expression to know when two intervals overlap: a1<b2, and b1>a2.

Here start_date and end_date are a1 and b1 respectively, and the two values you wrote '2018-09-15 03:00:00' and '2018-09-15 03:30:00' are thus a2 and b2 respectively.

We can thus make a query like:

begin='2018-09-15 03:00:00'
end='2018-09-15 03:30:00'

Lines.objects.filter(
    start_date__lt=end
    end_date__gt=begin
)

This thus does not check if the (begin, end) is "contained" in the (start_date, end_date), it checks if there is at least one element that is a member of both ranges.

Now the OVERLAP function in PostgreSQL is a bit more complicated, since it automatically swaps the times in case the end_date is greater than the start_date, so we might need to use .annotate(..) here:

from django.db.models import F
from django.db.models.functions import Greatest, Least

begin='2018-09-15 03:00:00'
end='2018-09-15 03:30:00'

Lines.objects.annotate(
    d0=Least(F('start_date'), F('end_date')),
    d1=Greatest(F('start_date'), F('end_date')),
).filter(
    d0__lt=end
    d1__gt=begin
)

This is still not exactly the same, since if d0 and d1 are the same, then the range is "inclusive", so we should consider this case as well:

Lines.objects.annotate(
    d0=Least(F('start_date'), F('end_date')),
    d1=Greatest(F('start_date'), F('end_date')),
).filter(
    Q(d0__lt=end, d1__gt=begin) |
    Q(d0=F('d1'), d0__gte=begin, d0__lt=end)
)

It also requires to "prepare" the begin and end (you should make sure that begin <= end, and if not swap these, not because otherwise the method will fail, but because these are the "exact" specifications of the rather complicated OVERLAP function).

Upvotes: 4

Exprator
Exprator

Reputation: 27523

YourModel.objects.filter(start_date__gte='2018-09-15 03:00:00',end_date__lte='2018-09-15 03:30:00')

this should do what you are looking for. Just replace your model name with the dummy provided.

Upvotes: 1

Related Questions