How to navigate the “interval” minefield
Presented by:
Bryn Llewellyn
Bryn Llewellyn is a Technical Product Manager at Yugabyte, Inc. YugabyteDB is an open source, cloud native distributed SQL database that looks like PostgreSQL to the developer. Bryn’s speciality is SQL and stored procedures in the context of distributed SQL.
Bryn has worked in the software field for more than forty years. He started working with SQL when he joined Oracle UK in 1990. He relocated to Oracle HQ (Redwood Shores, CA) in 1996 and his last role, before leaving, was as the Product Manager for PL/SQL. He left Oracle in April 2019 to join YugaByte, Inc.
Bryn started off doing image analysis and pattern recognition at Oxford University (programming in FORTRAN) and then worked in Oslo, first at the Norwegian Computing Center and then in a startup. In Norway, Bryn programmed in Simula—recognized as the first object-oriented programming language and as the inspiration for C++.
PostgreSQL’s date-time apparatus is vast and complex. This is partly explained by the inescapable reality of astronomy and human convention. Moreover, the timestamp and interval data types, and timezone sensitivity, came only after date, and the rules for date arithmetic, were already in use. The rules for the newer data types are different from the older rules for date values. (For example, subtracting date values gives an integer value; subtracting timestamp values gives an interval value.) Adding to this, early PostgreSQL versions implemented some questionable functionality. But the strict rules of functionality compatibility across database versions mean that this functionality lives on.
In other words, history has left us, here as well as in the physical world, with a minefield: one false step in your coding choices can bring dire consequences.
This session focuses on one aspect of the date-time story—and this is its most confusing aspect: the interval data type and the rules for interval-interval, and interval-timestamptz, arithmetic. I’ll explain critical notions that the PostgreSQL documentation covers so tersely that they are hard to grasp. And I’ll show you how easy it is to write code that brings profoundly unhelpful results—even though these are not considered to be wrong.
Then I’ll show you how three straightforward user-created domains with supporting functionality implemented in PL/pgSQL, for pure “months”, pure “days”, and pure “seconds” flavors of interval values, come to the rescue by bringing the safely constrained functionality that you need while robustly protecting you from disaster.
- Date:
- 2022 April 8 14:10 PDT
- Duration:
- 50 min
- Room:
- Santa Clara
- Conference:
- Silicon Valley 2022
- Language:
- Track:
- Dev
- Difficulty:
- Medium