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 lets you 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)
Speaking of data types, let’s begin with those used for storing date and time. Timestamps are a really popular type that’s used to store both at the same time. If you type in 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 their 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 28th, 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 29th, 2023 at 09:00 Greenwich Mean Time (GMT or UTC+0):