Time-related range types revisited

The presentation is focused on use cases that require versioning data or dynamically steering of time and date relevant information in a secure and efficient way. PostgreSQL has for these purposes built in range data types that simplify the management of such use cases and can be combined with other features of the database to achieve more complex targets.

PostgreSQL has built in support for a number of range data types, including number, date and timestamp ranges. In this presentation I will focus on the latter two. The need for date and time ranges, or ranges in general has always existed. First intents were done using two table fields for the limits and trigger functions to manage their values. The PostgreSQL implementation of the built in range types is a much more valid and flexible option that simplifies their usage and includes very efficient operators. They can be used in indexes for simple searches or exclusion requirements. So it becomes very simple to implement portions of business logic within the database, profiting from its advantages, such as reliability, efficient queries and, in particular, data integrity.

After a brief introduction to the data types and their functions and operators, I will show a set of real use cases from our daily business at the Institute for Economic Research in Zurich and from the Swiss PostgreSQL Users Group. The use cases make the main part of the presentation and include besides the usage of the range types, at least in one case, its combination with other PostgreSQL features.