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:

docs.yugabyte.com/latest/api/ysql/datatypes/type_datetime/

To see my slides and code used in the presentation, please access this .zip file.

Date:

2021 October 27 13:00 EDT

Duration:

1 h

Room:

Online

Conference:

2021 Postgres Conference Webinars

Language:

Track:

Dev

Difficulty:

Medium

Requires Registration:

Yes (Registered: 70)