The power of range types in Postgres

PostgreSQL has a little, but very powerful feature: range types

At first they seem like a “nice-to-have” feature, but especially when dealing with date ranges they are very helpful.

One type of problem where range types are extremely helpful is when testing overlaps for date ranges. While the overlaps operator makes testing for overlaps quite easy, range types make it very easy to also extract the actual overlapping range - the intersection between the two ranges:

select daterange(date '2019-06-22', date '2019-07-14', '[]') 
       * daterange(date '2019-05-16', date '2019-07-19', '[]') as intersection

The * operator does not “multiply” the two ranges - it calculates the intersection of them: the range that is common to both ranges.

The above returns

intersection           
-----------------------
[2019-06-22,2019-07-15)

Which means that the two ranges have the days from 2019-06-22 to 2019-07-14 in common.

This can e.g. be used in a booking system to find date ranges where “Product A” and “Product B” have been rented out at the same time or similar queries.

Having the start and end date for the intersection also enables us to easily calculate the number of days that this intersection represents - again something that would be quite complicated with standard SQL.