3 Improper data type usage
This chapter covers
- Avoiding using the wrong data type
- Time zone/daylight savings shenanigans
- Data types that should be avoided altogether
PostgreSQL is very rich in data types and probably supports more than most databases. It even goes a step further and allows you to define your own data types with their own indexes, functions, and operations! We will now take a look at some popular data types and how their use or misuse can lead to consequential mistakes.
3.1 TIMESTAMP (WITHOUT TIME ZONE)
Let’s begin with data types used for storing date and time. Timestamps are a really popular type that is used to store both at the same time. If you type TIMESTAMP, PostgreSQL will, by default, assume that you want TIMESTAMP WITHOUT TIME ZONE because that is a behavior required by the SQL Standard.
Our friends at Frogge Emporium have decided to use TIMESTAMP to hold when a customer service ticket was opened and when it was closed. Because of the way Frogge Emporium’s customer service system works, it stores the time a ticket was opened at the local time of the customer’s location.
Let’s take, for example, this ticket, opened by a customer on the US West Coast on October 28, 2023 at 16:00 Pacific Daylight Time (8 hours behind Universal Time Coordinate or UTC-8) and closed by a customer service agent (who was in the UK) on October 29, 2023 at 09:00 Greenwich Mean Time (GMT or UTC+0):