LinuxFest Northwest 2018: Make A Date With Postgres

Basil Bourque

This talk is a practical guide to the core concepts for handling date-time values with a Postgres database.

Working with date-time values is surprisingly tricky and complicated. Our intuitive understanding of the clock and calendar actually works against us as database admins and developers. Issues like time zones, offsets, UTC/GMT, Daylight Saving Time (DST), epoch, Leap Year, Leap Second, and historical anomalies create confusing complications. Add to that the very poor support for date-time handling in most databases and programming languages, and we have a troublesome mess.

Let’s walk through the fundamental concepts behind tracking time. First we cover how computers track time, with the epoch reference date, a count-from-epoch, and the resolution/granularity of that count. Then we move on to the exact meaning of UTC/GMT, offsets from UTC, and come to understand time zone as a history of those offsets.

With this understanding of zoned time in place, we move on to un-zoned “local” time. Through practical examples of scheduling and logistics planning, we learn when to use zoned time and when to use un-zoned time.

We explore these zoned and un-zoned notions of time first by looking at the core classes of the java.time framework newly added to Java. Some simple one-line programming examples exercise all the concepts discussed above.

We move on to map those concepts to the standard SQL data types for date-time, and to the data types found in Postgres. We practice these concepts with a series of business cases such as dental appointments, milking cows, and closing on the purchase of a home.

Along the way, some practical tips challenge you to shift your thinking to avoid confusion during date-time work. For example, we learn to not conflate date-time values with strings representing those values.

This guided tour of Postgres data types for date-time will leave you ready and certain when choosing the right type for your data. Replace your hope-and-prayer approach to date-time handling with a new confidence built on clear understanding. Handling date-time will be much easier, more predictable, and even enjoyable.