Home on the range with Django - getting comfortable with ranges and range fields with Jack Linke

Speaker 1: So I'm Jack. Um I this is actually my second year speaking with you all. Last year I was honored to uh have the opportunity to speak about HT uh HTMX. Uh use with with Django during the virtual twenty twenty-one conference. So as you can see on the screen, I run a business called WaterVise. And I am all of these things at the bottom of the screen, but I'm not sure that I've mastered any of them. So we'll see. Alright, so again, my talk is Home on the Range with Django , and we're going to talk about using Postgres range types. uh with your Django applications and why you might want to consider doing so.

Speaker 1: So a few disclaimers uh this this These topics are uh maybe a little more intermediate. Um some of you more advanced folks will probably uh think this is old hat, but uh And this is also Postgres specific. So if you're using a different database backend, this really won't apply to you, but hopefully in a future project you'll consider Postgres. It's a pretty awesome database. So the goals here, uh hopefully we'll learn why ranges can be more useful than having distinct start and end or min and max fields in your project. You'll get familiar with some of the terminology. We'll see a number of approaches to using and querying with ranges. And I'll give you some resources that

Speaker 1: will help you as you move forward if you'd like to use ranges in your app. applications. So real life ranges are are used all around us all the time. It's just not something we generally think about a lot. You know, daily temperature highs and lows. uh things like the range of calories in in uh a set of meals or business hours uh for a set of businesses. So we use them all the time. It's something that is in most of our applications in one way or another. The history of range fields, uh they've been around since 2011, the range types in uh Postgres and since twenty fifteen Django

Speaker 1: has had support for for these um When we look at projects, often we have kind of like I mentioned, a start and a stop field. So a start date and an end date, or a minimum date and a maximum date. Querying with these start and stop values can can get really complicated and uh challenging really quick. It's not always super intuitive, and we'll we'll look at some of that uh here. So some comparisons when we look at constraints, for instance, having two separate fields, the the database doesn't know that they're related in any way. So unless you add specific constraints to say, hey, uh, you know, the the min value needs to be lower than the max value, uh, the database doesn't know that.

Speaker 1: So you have to build some logic where you end up with uh weird things like ranges from $200,000 to $140,000, which just doesn't make sense. And then when it comes to preventing overlaps, if you want to prevent overlaps in your database, you have to kind of trick the database into treating those two fields as a single field to do this effectively with a um exclusion constraint. So probably not super easy to see and and you don't need to see the details really on this one, but this is everything that's required to add the functionality I just mentioned. And we add a check constraint on here to prevent a lower value from being larger than the higher value, and an exclusion constraint to prevent some overlaps.

Speaker 1: At the top you can see we have to add an additional TSTZ range function so that the database can again kind of get tricked into thinking that that period start and period end, those two fields are will be treated as a single field in in the constraint. Comparatively using uh a range field We just have one field and we're storing a good amount of information in a single field. The lower value, the upper value, and some information about the boundaries of that range. The database knows that they're all related. By default, Postgres won't let you put a lower value in the upper value spot. So you can't have backwards ranges.

Speaker 1: So right off the bat, it comes with some benefits there that you don't have to manually do that for the database. So you can see we end up with a much smaller uh the the same equivalent uh portion of the of the model here. So some comparisons with queries. Um again you you have to include both fields So these are often unintuitive. I found it really challenging as a beginner to understand how to do this right because you end up with things like this. In reality, the the event start, so if we have an event model with an event start and an event end, and we want to look at how that

Speaker 1: uh you know filter that down to over to The instances that it overlaps with in a 12-hour period, you have to do event start is less than the end of that period you're looking for, and event is Greater than or equal to uh the end is greater than or equal to the start of that period that you want. And that just as a beginner, that just doesn't make a whole lot of sense. uh when you when you kind of I had to literally draw it out visually to uh to understand that when I started out uh four or five years ago. So using a single intuitive lookup instead, uh you end up with this. If we have an event period which is a a date-time range field.

Speaker 1: Then we just have to look at does it overlap? It's as simple as that. There's no messing around with starts and ends and uh you know multiple um lookups. Just very simple. Does it overlap? So to make sure we we all understand what what these different lookups mean, uh we're gonna go back to elementary school, uh, but there won't be any tests, I promise. A few things right off the bat. If you come from like a math background or if you remember uh back in elementary school, you had the number line. And you have intervals and they could be closed intervals, open intervals. So so the terminology that Postgres uses may not be what you're familiar with, but these are kind of the

Speaker 1: equivalents. And then this is what it looks like, right? So on the very top, we have uh from from the number one to seven, if this is looking at an integer range. We have from the number one to seven, but that's exclusive of both of those. So that that range really includes the numbers two through six. And I'm I'm putting this up here. Again, this is not a test. This is just to make sure we all understand before we go on That second one down is really the one we're going to kind of focus on, and that's what Postgres and Django use by default, is a inclusive and exclusive range. So when you look at this uh this value, it includes the number one, that second one down again.

Speaker 1: It includes the number one, but it does not include the number seven. And that is the the default uh that uh Postgres uses. Another way to look at it is uh is just like this. Again, the second one down being that in this case 1 is less than or equal to x and 3 is greater than x. So we are not including 3. It is excluded. Kind of boring stuff, but uh here we go into the the visuals of it. So I I put together uh several slides here. Uh again, I'm I'm very much a visual person and I know I'm not alone in that.

Speaker 1: Uh so sometimes seeing how these different lookups uh that can be used when you're querying ranges, uh seeing it visually can can help. So this one is uh the first we're looking at when a range overlaps a range. And as you can see, the the two on the bottom, uh the one where it's uh zero inclusive to exclusive, uh it's It's almost touching two, but it's not touching two. It doesn't include two. So that one would get filtered out of this filter. Contains is another one. And the nice thing about uh contains is that it can be used with a single element or a range.

Speaker 1: So in this case, we're trying to uh filter And return any instances that contain the element of interest. So the number four there, the integer four is our uh uh what what we want to um uh filter on. So we're looking at anything that contains that, any of our instances that contain that. And the first two there, 0 to 8 and 4 to 9, do contain it. And again, 4 to 9, because that bottom number is inclusive, it includes that 4. Like I mentioned, you can also use contains uh with a range. So in this case, a range containing another range.

Speaker 1: And then contained by just reverses that relationship. So in this case, we're looking for anything that's within the range of one to eight, with eight being excluded. And this also can be used with uh a range compared to a range. Adjacent two lets you identify things that are right next to the range you're interested in. So uh if your hours of operation are from eight to ten , And you want to find out if there's anything immediately after uh that closing time, uh, this is what you would use. So the The range of interest here

Speaker 1: excludes the number six, which is why those two on the outside that are green are the way they are. And then fully less than and fully greater than just really mean is it all the way to the left of or all the way to the right of the range that we're looking at? So, how do we actually use these? The Psycho PG2 Extras uh package provides some some tools for translating between Django fields and the range types in Postgres. And there's a couple couple things to keep in mind here. So we've got numeric range and date range, and there's a couple on the next page.

Speaker 1: Uh when it comes to integers and dates, uh those are uh uh you know discrete values. All of the other ones, and and big inter range, sorry, uh decimal ranges and on this next page, date time range and date time tz range. uh those are not discrete they are continuous so you can have continually smaller uh you know periods be between things And that that can be important as we'll talk about later. So the the date time range, unless you're using naive date times, you're probably never going to use that. I wouldn't recommend it. Uh for most things if you're using time zones use the time zone TZ range uh from cycle

Speaker 1: PG to extras. And as you can see here, this shows the equivalent Postgres and equivalent Django fields that these map to. So when we're defining range fields in a Django uh model, uh this is some examples here. So using integer range field, we can have it with no additional information. It's just an empty range We can use a a tuple uh for a default, or we can use a numeric range. Again, that numeric range is coming from uh the Psycho PG to uh Extras package. And we can also specify the the bounds we're using here. So again, I mentioned that uh Postgres

Speaker 1: by default uses a uh Inclusive lower and exclusive upper, but uh you you can specify these these. It gets a little weird and uh it's It's uh something I'm not going to go into depth here because you kind of got to wrap your brain around when um it's appropriate to do so. And then there's some lookups that Django provides that are based on the range bounds themselves. The most useful ones that I use all the time are the starts with and ends with. These let you uh query on the lower and upper uh portions of a range.

Speaker 1: So a few examples here of of uh instance creation. Here we're defining uh three different ranges. So my first range is using a numeric range from uh that extras package. And we've defined the lower value as none, the upper value as none, and the bounds. I just kept it with the default inclusive exclusive. The second range down there, we're just using a tuple, one and five. And the third one, we're also using numeric range, but we have a lower value of seven and an upper value of none. We create a few objects in my model using those. And then on this next page, you can see how we are using the

Speaker 1: starts with and ends with. So if we look at starts with, we can add on uh additional lookups like greater than, less than, greater than, equal to. In this case, we're looking for any of those three that is greater than three in that that lower value. And uh obviously the the last one fits that uh category. And then ends with uh greater than uh ends with equals five there. Uh the only one that matches with that is the second range. You can also create your own range types. Uh Django and Postgres provide the functionality to do that. One example that you might be interested in, for instance, is

Speaker 1: uh taking the generic IP address field and extending it to be a range of IP field uh IP addresses. Um so if you're doing work uh you know on a network and you need to record a number of ranges um You know, this customer is operating from this IP address to this IP address. You can use the the ranges to to extend it to do that. And again, I'm not going into detail with that because we are limited on time, but the Django docs do a pretty good job of getting you started on how to extend the ranges for your own types. When it comes to indexing, uh pretty much stick to uh the gist

Speaker 1: index or the B tree index. Uh Those are the the two that really cover the majority of the lookups that we're interested in. Some of the other uh indexes like GIN don't don't cover uh any of the range stuff. So the kind of the the the big thing um that I'm hoping to bring to this talk uh is a a project I I've been developing. Um Uh it's it's not super uh beautiful, but uh it's simple and and hopefully will uh be useful to you all. So it's a pool scheduling application for a municipality. And the idea here is that we have

Speaker 1: a bunch of users and a bunch of pools. And each pool can have multiple lanes, multiple lockers, and maybe closed here and there. And then users can make reservations for these lanes and lockers. This is a little more detailed uh view of the the schema for the database. Uh and as you can see, I've tried to throw in uh several ranges here. uh as much as possible. So we have a couple integer range fields that are the depth uh and business hours for the pool, um the maximum maximum number of swimmers that a lane can have. Uh the reservations are from one time period to another, uh, date time period, I should say.

Speaker 1: And let me pull it up. So this is still somewhat of a work in progress, but I have a number of examples in here already. Again, the goal here was really to um help others that were in the same boat as me in the past. I I tried to find documentation and details for how to use range fields and I as much as I love Django docs for just about everything when it comes to range fields Uh if you're not familiar with them, it can be a real challenge to find useful stuff uh in the docs or even on like Stack Overflow. There's just uh there's not a whole lot of uh examples out there. So in this case, I've tried for for every uh page in this in this project, and you can go to

Speaker 1: the URL that I'll show you at the end. And it's just a Docker compose project. But for each one, we've got some details about what we're doing. So in this case, we're just listing the pools and the business hours. And for each uh time period here for our business hours, we're using dot lower. So pool. business hours dot lower and pools pool. business hours. upper to give the actual uh value here. And then we've uh you know zero padded it uh to to make it pretty.

Speaker 1: Again, um lots of examples here. So to show uh how you know the reservations that are greater than eight hours in length. Um You can see here I've just got some example data that's been mocked into the database. We have four reservations, four reservations for lanes that are over a period of eight hours. And when we go to the code notes, again it goes into some detail here, and I encourage you all to look at this later. But we uh we're essentially using the ends width and start starts with uh lookups that I mentioned earlier and filtering uh to annotate each one of those

Speaker 1: uh with with the start time subtracted from the end time. And then we add a filter after that uh to get the the delta, any delta that is greater than uh a time delta time zone time delta of eight hours. For reservations this week, uh either starting on a Sunday with this first example or a Monday with the second example. We can see the results in this calendar here. This shows all for this week. So starting on the 16th and going towards the end to the end of the week.

Speaker 1: There are all of our examples. And for this one, uh created just a simple uh utility in a separate file to to pull the uh start and end. uh date time for this week and and next uh and then filtering on that uh similar to to the other one um using the the overlap this time. So uh as I mentioned before, uh overlap simplifies your Queries when it comes to taking one date time range and another date time range and identifying if they overlap or not, this makes it very simple.

Speaker 1: Also showing here are examples for uh all the reservations this month. Any past reservations, reservations year to date or from now till the end of the year. This application has just a simple check-in checkout functionality. For reservations, you can compare then if if the reservation, if they've checked in for the reservation or checked out, or if they haven't, this will show you if there's an overdue start. So for instance here, these three reservations, they never checked in. And that is uh performed by looking at again the starts width and then using less than now.

Speaker 1: And for the actual check-in date time range starts with, and we look for something that's null. So they they never checked in. One of the things that was really challenging uh as a beginner also was working with uh date times. Uh And other ranges when it came to forms. So I have three different examples here of ways you might approach using ranges in your forms. First being manually rendering uh the the values. So in this case, uh I manually put a uh input tag

Speaker 1: into the template. Uh it's not actually using forms. This is using uh HTMX so that when I select uh some date times here. And apply. We're using HTMX to send a post request, which then the the input or I'm sorry, the uh the response from that gets uh input into a div. And uh displayed here. And as as somebody who has come to really like uh HTMX, uh I I think that might just be the the easiest way. But uh it doesn't come with uh Um, you know, the validations that that Django Forms does. So if we look at uh

Speaker 1: Django Forms This example is very similar, but what we've done is use a text field, and then I'm cleaning in the back end. So it looks just about the same. When we apply, you get the results. But again, we've got the form with a char field in this case and a text input for the widget And then we are cleaning uh that that text uh to convert it into an actual value that we can use. Um in this case uh We've we've attached uh

Speaker 1: using jQuery, we've attached uh a date time range picker. Uh actually all these examples do. Um You could use uh obviously you know a different uh JavaScript library or different means of displaying uh that date time range, but uh that was convenient here. And then the last one is to use a true date time range field from Django. And in this case, it displays it as a split multi-widget. uh with two different date time fields. And so again, I just attached uh using the same I am using bootstrap uh date range picker here. Um you could use whatever you like, but uh We select a range, submit, and uh

Speaker 1: we get some results. We've got another number of other examples that are still in progress that will show some more advanced filtering and database work when it comes to using ranges. But hopefully this will be a good start to look at and beneficial to you all. There are some uh pitfalls when it comes to using ranges. Uh Uh support in the Django admin is not really great. Uh and there's actually a ticket open that kind of talks about that. Uh the concern was that

Speaker 1: um uh you know modifying Django admin to bring in uh contrib Postgres uh content to to change it uh would would be problematic and and kind of um mixing things that maybe shouldn't be mixed. Uh you can of course extend the default uh multi-widget used in Django's admin. uh with with your own template and then use a similar technique to what I was showing earlier and you know attach a uh picker of some sort. But by default, you just get these text boxes, uh, and it can be a little um off-putting, I guess, uh trying to modify your ranges in the admin

Speaker 1: Trying to use lower and upper as callables. They're not callables. So when it comes to querying, you can use the lower and upper database functions to specifically pull out those pieces of the range. And then when it comes to using lookups, again you can use starts with and ends with to pick out those pieces. And using uh F with the extra classes, um F is is You know, used within Django to refer to the contents of a field. Uh but the Psycho PG2 extra classes are not Django classes, and they are not

Speaker 1: Uh Postgres types, they are uh kind of that translation layer between the two. Um so you may have to use cast if you're if you're trying to uh pick out uh for instance to to conduct um aggregation or or annotation and you would typically use uh F, you may have to use cast and cast those to an actual uh Django range type to be able to do that So there are a number of resources when it comes to uh working with ranges. So the Psycho PG2 extras uh documentation itself uh goes into some some good detail about uh how you use those different classes

Speaker 1: and uh the functionality they provide. Obviously the Django docs is a good place to start and I've benefited a lot from looking at the the tests as well. When it comes to understanding kind of any deeper thing in Django , or probably Python as a whole, looking at tests can be a huge benefit. This talk and the example project that I mentioned are on GitHub, and I encourage you to grab that and uh There 's very simple instructions for using it with Docker Compose to pull it up for yourself. And then a couple packages that that I uh have worked on in the past that

Speaker 1: uh I found very useful when it comes to working alongside ranges are the uh range merge package and it just takes Postgres 's range merge function for aggregates and allows you to do things like if you're filtering and you have uh one range way over here and one range here. So say uh from one to two and from seven to eight. Uh range merge allows you to get from one to eight uh returned, a single uh full range that that uh covers the entirety uh of of everything that's been filtered. And then Django Generate Series, very similarly, just uh helps you use Postgres 's generate series to make sequences, which can be really helpful if you want to, for instance, have a denormalized

Speaker 1: uh database table of bucketed times or bucketed integer ranges. If you don't want to drop down to raw SQL, you can use that package to generate those. All right, and one final thought uh because you got to leave this on a tacky uh last thing. A man works hard to name an interval equal to 24 hours. So he calls it a day.

Speaker 2: So we do have a bunch of time if anyone would like to ask a range of questions. Well actually one per

Speaker 1: You should have given the talk.

Speaker 2: I did one yesterday Hi

Speaker 3: Jack, thank you. It's r really good. I had a sort of half thought. Was it say I've got um a model with two daytime fields and I haven't used a a range. Can I query with a range object? Is that

Speaker 1: Say that again.

Speaker 3: So so I've got a model and I've got to start it out and I've finished that because I didn't think oh I know I'm gonna use a range field. Um can I is there a sort of way I can query with a range object against that existing

Speaker 1: So you you can and uh I'm gonna go back to uh towards the beginning when uh we were looking at the constraints. Um So this example from um uh Django's docs actually about uh Postgres constraints. Um here they use a TSTZ range uh to um use that function within the Postgres database. And so you can use very similar to what they do down at the bottom, or or I guess it's kind of in the middle, is they have uh an expression there where they're using that function and then combining those two. uh period start and period end being the the two distinct fields here. And then they're using range boundary. That just gives you the default uh

Speaker 1: inclusive exclusive bounds. So that that's uh generally how you would uh accomplish that. Uh

Speaker 4: thanks for the talk. I have several models that have start and end dates that could have been ranges Uh what's the best strategy for converting a model to having a range instead of two fields?

Speaker 1: Good question. Uh I cheated. I was early enough in in my in my project that um I I did what you're never supposed to do and uh I deleted everything in the database and all the migrations and started over from scratch. You'd probably want to do a sequence of uh migrations where you add that field. populate it uh again doing something very similar here right um taking those two uh distinct fields and applying their value um to the new field and then uh eventually removing that field. There was actually there was a really good talk yesterday about migrations. I'd encourage you to watch that video after this

Speaker 1: or whenever it uh comes up on the uh Django Con site. um for for some tips on how to make those sorts of changes. Well thank you all very much. It was a pleasure speaking with you.