Postgres DateRangeField stores "empty" in the database if lower and upper dates have the same value

Using Django Using the ORM
1

Hi,

I use the Postgres specific DateRangeField (Postgres 14, Django 5.0.1) and a slightly modified models example from the documentation.

I run into a problem when I set the lower and upper date values to the same value. In that case, the database doesn’t store the date. Instead it stores empty as a value and the date information itself seems to be lost.
I understand that in Postgres, empty means that the range doesn’t contain any point (see PostgreSQL: Documentation: 16: 8.17. Range Types) but that doesn’t make sense in my usecase (or in the linked django example) where the date is relevant.

Here’s my code (as I said, slightly modified from the django documentation example, and yes, I have the BtreeGistExtension() setup via a migration):

from django.contrib.postgres.constraints import ExclusionConstraint
from django.contrib.postgres.fields import DateRangeField, RangeOperators
from django.db import models
from django.db.models import Q

class Room(models.Model):
    number = models.IntegerField()


class Reservation(models.Model):
    room = models.ForeignKey('Room', on_delete=models.CASCADE)
    date = DateRangeField()

    class Meta:
        constraints = [
            ExclusionConstraint(
                name='exclude_overlapping_reservations',
                expressions=[
                    ('date', RangeOperators.OVERLAPS),
                    ('room', RangeOperators.EQUAL),
                ],
            ),
        ]

Using this code now with an example that works as expected:

>>> r1 = Room.objects.create(number=1)
>>> res1 = Reservation.objects.create(room=r1, date=['2000-01-01', '2000-02-01'])
# now trying to add another reservation with the same room with an overlapping date doesn't work (as expected)
>>> res1 = Reservation.objects.create(room=r1, date=['2000-01-01', '2000-01-12'])
django.db.utils.IntegrityError: conflicting key value violates exclusion constraint "exclude_overlapping_reservations"
DETAIL:  Key (date, room_id)=([2000-01-01,2000-01-12), 3) conflicts with existing key (date, room_id)=([2000-01-01,2000-02-01), 3).

Now the unexpected (buggy?) part:

# just to be sure there's nothing else
>>> Room.objects.all().delete()
>>> Reservation.objects.all().delete()
# start here creating things
>>> r = Room.objects.create(number=1)
>>> res1 = Reservation.objects.create(room=r1, date=['2000-01-01', '2000-02-01'])
# so far so good - but why does the next command work? the date **does** overlap
>>> res2 = Reservation.objects.create(room=r1, date=['2000-01-15', '2000-01-15'])
>>> res2
<Reservation: Reservation object (11)>
# and why can we create multiple objects with the same date?
>>> res3 = Reservation.objects.create(room=r1, date=['2000-01-15', '2000-01-15'])
>>> res3
<Reservation: Reservation object (12)>
>>> res3.date
['2000-01-15', '2000-01-15']
# and doing another query shows that the date is empty
>>> Reservation.objects.all()[0].date
DateRange(datetime.date(2000, 1, 1), datetime.date(2000, 2, 1), '[)')
>>> Reservation.objects.all()[1].date
DateRange(empty=True)
>>> Reservation.objects.all()[2].date
DateRange(empty=True)

Looking at the database now:

$ ./manage.py dbshell
psql (15.5 (Ubuntu 15.5-0ubuntu0.23.10.1), server 14.10 (Debian 14.10-1.pgdg120+1))
Type "help" for help.

example=# select room_id, date from example_reservation;
 room_id |          date           
---------+-------------------------
       4 | [2000-01-01,2000-02-01)
       4 | empty
       4 | empty
(3 rows)

So I see 2 problems here:

  1. there seems to be now way to store a single day daterange with the DateRangeField model field.
  2. it’s possible to create multiple DB entries with empty daterange values

Any ideas if that is a bug or am I missing some configuration/constraints/whatever ?

2

This is not accurate.

A DateRangeField of res1 = Reservation.objects.create(room=r1, date=['2000-01-01', '2000-01-02']) consist of a range containing one date - 2000-01-01. The date 2000-01-02 is not in that range. As defined in the docs for DateRangeField, the range is defined as including the lower bound but excluding the upper bound. (Also notice the representation as presented by the SQL statement you show.)

In other words, a DateRangeField defined with [‘2000-01-01’, ‘2000-01-02’] does not overlap with either [‘1999-12-01’, ‘2000-01-01’] or [‘2000-01-02’, ‘2000-01-31’].

1 Like