Using DateTimeRangeField in Django

The basics

If you need to store a datetime range in Django (for instance from which day to which day a listing is valid), instead of relying on two fields like valid_from and valid_to, you can use a single field validity_range of type DateRangeField. This way, both values are stored together in the same field. Since DateTimeRangeField is specific to PostgreSQL, you must import it like this from django.contrib.postgres.fields import DateRangeField.

You can assign a value to this field directly with a tuple:

# Create a range from 2021-01-01 to 2021-04-01
instance.validity_range = ('2021-01-01', '2021-04-01')

You can also leave either bound unbound by setting it to None:

# Create a range from 2021-01-01 to the end of times.
instance.validity_range = ('2021-01-01', None)

You can also create the range explicitly with DateTimeTZRange (that's what you will get from the database anyway):

from psycopg2.extras import DateTimeTZRange
instance.validity_range = DateTimeTZRange(lower='2021-01-01', upper='2021-03-31')

This will also allow you to select the behavior of the boundaries: should they be included or excluded. By default, the lower boundary is included while the upper one is excluded. Please refer to the documentation for more details.

You can them query your models like this:

MyModel.objects.filter(validity_range__contains=date.today())
MyModel.objects.filter(validity_range__endswith__lte=date.today())
MyModel.objects.filter(validity_range__startswith__gt=date.today())

And many more! See here to view everything that is available.

Constraints

One very interesting things you can do with these fields is add an exclusion constraint at the database level to prevent ranges to overlap. You can also prevent overlap with conditions. For instance, to take back our listing example, you can prevent overlap across all customers with:

from django.contrib.postgres.constraints import ExclusionConstraint
from django.contrib.postgres.fields import DateTimeRangeField, RangeOperators

class Meta:
    constraints = [
        ExclusionConstraint(
            name='exclude_overlapping_validity_range',
            expressions=[
                ('validity_range', RangeOperators.OVERLAPS),
            ],
        ),
    ]

Or only for each customers:

from django.contrib.postgres.constraints import ExclusionConstraint
from django.contrib.postgres.fields import DateTimeRangeField, RangeOperators

class Meta:
    constraints = [
        ExclusionConstraint(
            name='exclude_overlapping_validity_range',
            expressions=[
                ('validity_range', RangeOperators.OVERLAPS),
                ('customer', RangeOperators.EQUAL),
            ],
        ),
    ]

See the documentation for a more complete example and the list of all options.

Discrete intervals

One thing to note is that in this case, ranges cannot share a date. For instance, if a range ends at 2021-01-01, another cannot start at the same date, even if only one range actually includes the value.

To prevent this, for most ranges, you can end the range just before the new one start. For instance, with integers, you can do this [0-9] and [10-20]: since there is no integer value between 9 and 10, our ranges are contiguous, without holes between them. The same reasoning can be easily applied to dates. It can also be applied for datetimes: the minimum time resolution PostgreSQL supports is the micro-second. So, if one range ends one micro-second before the next one start, we are always in one range or the other and never between ranges. We will be in a discrete range.

The same logic should apply to decimals when they have a fixed precision and thus cannot allow numbers between two values, if those values are "close enough" (that is separated only by the smallest value you can represent at this precision) you will be in either interval, never in between. I don't think this can ever work with float though.