You thought you were out of the woods because we've covered timestamps, dates, times, time zones, surely that's it. No, thank you, Postgres, we have it all. We're going to talk about intervals, which instead of a discreet point in time is a duration. You can think of an interval as a duration. When we get to the querying section of this course, which this video is not in that section, when we do get to that section, we'll see that intervals make certain queries that are otherwise complicated, quite simple.

If you want to find if two ranges overlap. If you're booking out a conference room you need to see, do two bookings overlap or rather, do you want to prevent all bookings from overlapping? Probably. Intervals can make that very easy. We can also find if multiple, multiple ranges overlap instead of just two. We can find out multiple ranges. We can find if a point in time exists inside of a given interval or range or duration. That's kind of nice.

In this video, I'm just going to show you how to create some intervals, not necessarily how to do all the querying magic against them, we will get there. There are a few use cases for intervals. It is not the most common type, it's good for you to know that it exists because likely if you don't know that this exists, you'll make it up on your own, which would be fine, Postgres has affordances there for you for both the creation the querying of those types.

Let's take a look at how we can define these intervals. The first way to create an interval is by using the Postgres standard format, which we are going to then cast to the interval type. That format is unit then quantity then kind of just over over over for as much as you want to do. You could say one year as an interval, may I just give you back one year. Let's keep going, two months, three days, four hours, five minutes , don't type , six seconds. If we do that, then we get back a slightly less verbose style, which means we can maybe compress this down to 04:05:06. Do we get the same thing? We sure do. Now, just like date style, interval style is configurable. The interval style right now is set to Postgres, which is fine. This just controls the output style. This just controls this format that we see here. We could of course say that interval style equals our dear old friend iso_8601.

Now if we run that, we see something that is worse. I don't know if it's worse, here is what it is, is much shorter. It's less verbose. What we're looking at here is this, it must start with a P. That is iso_8601 standard. Then you have all of these abbreviations. It's YMD, HMS, all capitals. You can just put the numbers there in the middle. For this, it is separated by a T to denote the start of the time. That is the verbose, or sorry, less verbose format. The iso_8601, there is an alternate iso_8601.

It still starts with a P, then it looks a lot more like a timestamp. You have your four digit year, your two digit month, two digit days, then a T, then the hours, then the minutes, then the seconds. If we were to run this, you see how this looks just like a timestamp except that 001 is, you know, that's a very early year, it starts with P then has T in the middle. If we ran that, we get that back. Let's switch the interval style back to Postgres then run that again. We do get one year, two months, three days, four hours, five minutes six seconds.

I don't have a lot of opinions on which interval style you use. Postgres is the default seems totally legit to me. I don't really care about that. You decide what works best for your use case, now you know that both of them exist. There's at least one other way to declare an interval that is to say interval. Then you can say 2 year. You could do that that will give you two years. You could also say interval one to six then year to month, you could do something like that. That gives you one year, six months, you could say 6000 second. That is going to give you one hour 40 minutes.

What's interesting here is the way that Postgres stores these intervals under the hood, it stores discreetly some of the date parts. Because if we were to say, let's say, you know, two months 12 days, you can't really just convert that to days, right? You can't say, well, a month has 30 days. Nope, game over. You lose because a month doesn't have 30 days. Under the hood, Postgres is going to do its best to store them in discreet units such that it's still going to work across daylight savings time, saving time, sorry about that. Months that are short or long due to leap years, stuff like that.

Intervals are pretty safe to store durations or periods of time because of the way that they are stored under the hood. These get to be a lot more fun when it comes time to querying, which we will look at a little bit later.