Timestamps, timezones, and interval arithmetic: what you need to know, and what you don’t need to know
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++.
No video of the event yet, sorry!
Anecdotal reports indicate that PostgreSQL neophytes are daunted by the date and time data types, and by how operations that use values of these data types might be affected by the session's timezone setting. They find the topic to be mysterious and frightening. Even experienced developers struggle when they first embark on a critical project that relies on this functionality.
I recently completed a careful and exhaustive study of the topic aiming for total understanding, complete in breadth and depth, so that I could write it all up in the YugabyteDB documentation*. The exercise left me with these two high-level conclusions:
- PostgreSQL, and therefore YugabyteDB, give you sufficient functionality to let you straightforwardly and correctly meet any requirement that might be set in the date-time space.
- They also provide far more functionality than a correct implementation will need—which surplus serves only to give you enough rope to hang yourself.
This is why I chose my talk's title.
I'll show you some use cases and what I consider to be the best ways to meet their requirements. I'll also show you some code that certainly works, at least after a lot of study and thought, as you'd expect. And I'll convince you that any reasonable requirement can be met without using such code and that you're best off avoiding it. I'll show you how to define some utility functions that encapsulate the native functionality to allow what you need to do and fence off what I consider to be harmful.
I'll explain what lies behind statements like this, from the PostgreSQL documentation:
- Internally, interval values are stored as months, days, and seconds. This is done because the number of days in a month varies, and a day can have 23 or 25 hours if a Daylight Savings Time adjustment is involved... Because intervals are usually created from constant strings or timestamp subtraction, this storage method works well in most cases, but can cause unexpected results.
so that you can transcend the feeling of fear, uncertainty, and doubt that they engender.
— — — — — — — — — — — — — — — — — — — —
[*] In case you don't know, YugabyteDB is an open source cloud native distributed SQL database. It re-uses the PostgreSQL "upper half" code for SQL processing and it replaces the PostgreSQL "lower half" code for data persistence and retrieval with its own code that uses a model inspired by Google Spanner.
Here is the direct URL to the documentation that I wrote:
To see my slides and code used in the presentation, please access this .zip file.
- 2021 October 27 13:00 EDT
- 1 h
- 2021 Postgres Conference Webinars
- Requires Registration:
- Yes (Registered: 70)