Filter Django RangeField by comparing to a point, not to another range
The PostgreSQL specific model fields docs are very specific about how to compare one RangeField to another range. But how do you compare a range to a single point?
For example, if I've got a model with valid_range=DateTimeRangeField, and I want to find all instances which are no longer valid, I need to do something like:
from django.utils import timezone as tz
MyProduct.objects.filter(valid_range__lt=tz.now())
But this isn't allowed. I thought I could use fully_lt but that's not allowed with a particular date either.
How do I filter a DateTimeRangeField to find instances whose ranges ended before a certain datetime?
The answer is kind of given in the docs but it's very subtle:
endswith
Returned objects have the given upper bound. Can be chained to valid lookups for the base field.
In other words endswith can act as an alias for the upper bound of a range.
So the equivalent of this in Postgres-flavoured SQL:
WHERE upper(date_range) < '2050-01-01'
is:
.filter(date_range__ends_with__lt=datetime(2050, 1, 1))
You can see this by adding .query to the end of your QuerySet definition:
>>> print(MyProduct.objects.filter(valid_range__endswith__lt=(datetime(2050, 1, 1))).query)
...WHERE upper("myproduct"."valid_range") < 2050-01-01 00:00:00+00:00
As always when working with ranges, think carefully about whether the endpoint of the range is included or excluded. (By default the end is excluded from the range.)