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
* 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.