About This Talk
Objectives
Audience members will learn why ranges are more useful than distinct start and end values, will become familiar with range-based terminology, will have the opportunity to see a number of approaches to using and querying with ranges, and will have resources for further reading and learning. These resources will include a link to a GitHub repository containing the examples from the talk, additional examples, and a cheatsheet for working with ranges.
Outline
The naive approach to ranges (2 min)
- Using separate start and stop model fields
- Querying with start and stop values
- Quickly gets complicated
Range visualization for concrete understanding (3 min)
- Terminology
- Inclusive vs Exclusive
- Overlap
- Contains
- Contained By
- Comparisons (fully_lt, fully_gt, etc)
A before-and-after look at the models (5 min)
The example project is a Swimming Pool Resource Scheduler that makes heavy use of ranges (probably more than would be used in most projects) in order to demonstrate various approaches. The model layout can be visualized in the following diagrams:
We will look at the models before and after using range fields.
The initial (stripped down) models.py file using distinct fields for lower and upper values is:
class Pool(models.Model):
"""An instance of a Pool. Multiple pools may exist within the municipality"""
name = models.CharField(_("Pool Name"), max_length=100)
address = models.TextField(_("Address"))
depth_minimum = models.IntegerField(_("Depth Minimum"), help_text=_("What is the depth in feet of the shallow end of this pool?"))
depth_maximum = models.IntegerField(_("Depth Maximum"), help_text=_("What is the depth in feet of the deep end of this pool?"))
business_hours_start = models.IntegerField(_("Business Hours Start Hour"), default=9)
business_hours_end = models.IntegerField(_("Business Hours End Hour"), default=17)
class Meta:
verbose_name = _("Pool")
verbose_name_plural = _("Pools")
class Closure(models.Model):
"""A way of recording dates that a pool is closed"""
pool = models.ForeignKey(Pool, on_delete=models.CASCADE, related_name="closures")
start_date = models.DateField(_("Pool Closure Start Date"))
end_date = models.DateField(_("Pool Closure End Date"))
reason = models.TextField(_("Closure Reason"))
class Meta:
verbose_name = _("Closure")
verbose_name_plural = _("Closures")
class Lane(models.Model):
"""Each pool may have multiple lanes, each of which can be reserved by multiple people"""
name = models.CharField(_("Lane Name"), max_length=50)
pool = models.ForeignKey(Pool, on_delete=models.CASCADE, related_name="lanes")
max_swimmers = models.PositiveSmallIntegerField(_("Maximum Swimmers"), )
per_hour_cost = models.DecimalField(_("Per-Hour Cost"), max_digits=5, decimal_places=2)
class Meta:
verbose_name = _("Lane")
verbose_name_plural = _("Lanes")
class Locker(models.Model):
"""Each pool may have multiple lockers, each of which can be reserved by only one person at a time"""
# Using CharField, because sometimes locker number might be "A23" or "56-B"
number = models.CharField(_("Locker Number"), max_length=20)
pool = models.ForeignKey(Pool, on_delete=models.CASCADE, related_name="lockers")
per_hour_cost = models.DecimalField(_("Per-Hour Cost"), max_digits=5, decimal_places=2)
class Meta:
verbose_name = _("Locker")
verbose_name_plural = _("Lockers")
class LaneReservation(models.Model):
"""A lane reservations defines a set of users, a period of time, and a pool lane"""
users = models.ManyToManyField(User, on_delete=models.CASCADE, related_name="lane_reservations")
lane = models.ForeignKey(Lane, on_delete=models.CASCADE, related_name="lane_reservations")
period_start = models.DateTimeField(_("Reservation Period Start"))
period_end = models.DateTimeField(_("Reservation Period End"))
actual_start = models.DateTimeField(_("Actual Usage Period Start"))
actual_end = models.DateTimeField(_("Actual Usage Period End"))
cancelled = models.DateTimeField(_("Reservation is Cancelled"), null=True)
class Meta:
verbose_name = _("Lane Reservation")
verbose_name_plural = _("Lane Reservations")
class LockerReservation(models.Model):
"""A locker reservation defines a user, a period of time, and a pool locker"""
user = models.ForeignKey(User, on_delete=models.CASCADE, related_name="locker_reservations")
locker = models.ForeignKey(Locker, on_delete=models.CASCADE, related_name="locker_reservations")
period_start = models.DateTimeField(_("Reservation Period Start"))
period_end = models.DateTimeField(_("Reservation Period End"))
actual_start = models.DateTimeField(_("Actual Usage Period Start"))
actual_end = models.DateTimeField(_("Actual Usage Period End"))
cancelled = models.DateTimeField(_("Reservation is Cancelled"), null=True)
class Meta:
verbose_name = _("Locker Reservation")
verbose_name_plural = _("Locker Reservations")
The final (stripped down) models.py with range fields is:
class Pool(models.Model):
"""An instance of a Pool. Multiple pools may exist within the municipality"""
name = models.CharField(_("Pool Name"), max_length=100)
address = models.TextField(_("Address"))
depth_range = IntegerRangeField(
_("Depth Range"),
help_text=_("What is the range in feet for the depth of this pool (shallow to deep)?"),
)
business_hours = IntegerRangeField(_("Business Hours"), default=(9, 17))
class Meta:
verbose_name = _("Pool")
verbose_name_plural = _("Pools")
class Closure(models.Model):
"""A way of recording dates that a pool is closed"""
pool = models.ForeignKey(Pool, on_delete=models.CASCADE, related_name="closures")
dates = DateRangeField(_("Pool Closure Dates"))
reason = models.TextField(_("Closure Reason"))
class Meta:
verbose_name = _("Closure")
verbose_name_plural = _("Closures")
class Lane(models.Model):
"""Each pool may have multiple lanes, each of which can be reserved by multiple people"""
name = models.CharField(_("Lane Name"), max_length=50)
pool = models.ForeignKey(Pool, on_delete=models.CASCADE, related_name="lanes")
max_swimmers = models.PositiveSmallIntegerField(
_("Maximum Swimmers"),
)
per_hour_cost = models.DecimalField(_("Per-Hour Cost"), max_digits=5, decimal_places=2)
class Meta:
verbose_name = _("Lane")
verbose_name_plural = _("Lanes")
class Locker(models.Model):
"""Each pool may have multiple lockers, each of which can be reserved by only one person at a time"""
# Using CharField, because sometimes locker number might be "A23" or "56-B"
number = models.CharField(_("Locker Number"), max_length=20)
pool = models.ForeignKey(Pool, on_delete=models.CASCADE, related_name="lockers")
per_hour_cost = models.DecimalField(_("Per-Hour Cost"), max_digits=5, decimal_places=2)
class Meta:
verbose_name = _("Locker")
verbose_name_plural = _("Lockers")
class LaneReservation(models.Model):
"""A lane reservations defines a set of users, a period of time, and a pool lane"""
users = models.ManyToManyField(User, related_name="lane_reservations")
lane = models.ForeignKey(Lane, on_delete=models.CASCADE, related_name="lane_reservations")
period = DateTimeRangeField(
_("Reservation Period"),
validators=[
DateTimeRangeLowerMinuteValidator(0, 30),
DateTimeRangeUpperMinuteValidator(0, 30),
validate_zeroed_dt_sec_microsec,
],
)
actual = DateTimeRangeField(_("Actual Usage Period"), default=(None, None))
cancelled = models.DateTimeField(_("Reservation is Cancelled"), null=True)
class Meta:
verbose_name = _("Lane Reservation")
verbose_name_plural = _("Lane Reservations")
class LockerReservation(models.Model):
"""A locker reservation defines a user, a period of time, and a pool locker"""
user = models.ForeignKey(User, on_delete=models.CASCADE, related_name="locker_reservations")
locker = models.ForeignKey(Locker, on_delete=models.CASCADE, related_name="locker_reservations")
period = DateTimeRangeField(_("Reservation Period"))
actual = DateTimeRangeField(_("Actual Usage Period"), default=(None, None))
cancelled = models.DateTimeField(_("Reservation is Cancelled"), null=True)
class Meta:
verbose_name = _("Locker Reservation")
verbose_name_plural = _("Locker Reservations")
Example Project Walkthrough (30 min)
The models in this project will be used to demonstrate a variety of tasks in django views, including a number of the following:
- Set constraints for the various range fields
- Access the lower and upper values of a range fields in views and templates
- Get reservations that start at a specific datetime
- Get reservations that overlap with a single date
- Get reservations that overlap with a range
- Check if a reservations start datetime matches any value in a list
- Get reservations with a start or end that falls between two dates
- Get reservations whose lower/upper date falls within a range
- Get reservations for the current week
- Get reservations that end before now
- Get reservations that start on or before September
- Get reservations that end in May OR September
- Given a list of datetimes, get all reservations that overlap with the items in the list
- Order the queryset by lower. If two reservations have the same lower, also sort by upper. (This is the default behavior in django)
- Get reservations with an overdue start (the reservation time started, but the party has not yet checked in)
- Get reservations with an overdue end (the reservation time ended, but the party has not checked out)
- Given a datetime (and other filtering criteria), get the count of reservations at that moment
- For a particular Lane (or set of Lanes), get the aggregate count/sum of reservations during each hour of a daterange
- Sum of all swimmers at a given moment
- Sum of all swimmers at each time period change
- For a particular Lane or entire Pool, get the time and value of all changes in number of swimmers
- Given a start time stop time and a Lane, return a queryset of the swimmers at each 15 minute increment.
- Calculated overall usage (number of swimmers * time interval = swimmer hours) within a time range
- Total usage by day/week/month by Pool/Lane
- Usage by range: Given a list of ranges, calculate the usage during each range
- Prevent overlapping reservation_period for the same resource (here, it’s Rooms), where the reservation has not already been cancelled
- Add validation to model field for minimum and maximum datetimes
- Aggregate the minimum Lower and maximum Upper reservations dates for all reservations in a queryset
- Similarly, annotate the minimum Lower and maximum Lower reservations dates for all reservations in a queryset
- Assuming each reservation is associated with a Resource, annotate Resources with the most recently ending reservation (similar for most recent starting or longest-ago starting/ending reservation)
- Multiple ways of saving a model instance with DateTimeRangeField
Note: Those examples above which we are unable to cover during the talk can be viewed in the example project GitHub repo which will be provided for the talk
Resources (5 min)
- psycopg2.extras
- This talk & Example Project
-
django-range-merge - use
range_mergeaggregate with Django - django-generate-series - create sequences with Django’s ORM
Presenters
- django-generate-series - Makes it easier to use Postgres’ generate_series to create sequences with Django’s ORM
- django-model-info - Provides a Django Management Command for displaying details about your project’s models such as listing model fields, relations, methods, and more
- django-postgresql-dag - Directed Acyclic Graphs with a variety of methods for both Nodes and Edges, and multiple exports
- django-calendardate - A Django calendar model with date metadata for querying against
- django-htmx-todo-list - An example of todo list application using Django and HTMX
Jack Linke
Jack Linke tends to learn the hard way - and shares the lessons from those experiences with others through blogging, tweets, and speaking engagements. He has been developing software and hardware projects off-and-on for most of his life, but much of his relevant web development experience has been hard-earned over the past four years during development of Watervize - a B2B2B SaaS web application (written in Django) to help irrigation water utility companies improve efficiency, analysis, and communication with staff and agriculture customers.
Jack’s technology interests include Python, the Django project, HTMX, GIS, graph theory, data storytelling, and visualization. He is a frequent contributor to the open source community and a contributing member of the Python Software Foundation.
His speaking experience includes briefing Generals on topics ranging from budgets to technical concepts, instructing at formal training centers, providing software demonstrations and feature walk-throughs, and he was certified as a Weapons and Tactics Instructor (WTI) while serving as a Marine Corps Officer.
Outside of coding, Jack solves unusual math and logic problems, and makes a mess in the kitchen.
Open Source Projects:
Email: jack@watervize.com
Twitter: https://twitter.com/JackDLinke
Polywork: https://www.polywork.com/jacklinke
Website (personal): https://www.jacklinke.com
Website (Watervize): https://www.watervize.com