Right, let’s talk about one of the most common, and most insidious, database design mistakes I see: storing a timestamp without its time zone. You might as well be storing a photograph without the context of who’s in it or when it was taken. You have the data, but its entire meaning is ambiguous. It’s a promise you can’t keep, and it will come back to haunt you.

The core of the problem is this: a timestamp without time zone information (timestamp in PostgreSQL, DATETIME in MySQL, datetime in SQLite) is just a vague point on the calendar and clock. It has no inherent meaning. Is it 2023-10-26 14:30 in London? In New York? On the International Space Station? You simply don’t know. The database will happily store whatever string of numbers you give it, blissfully unaware that it’s holding a semantic mess.

The Naive Approach and Its Inevitable Failure

Let’s say you build a simple event logging system. You create the table like this and call it a day.

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    description TEXT,
    created_at TIMESTAMP -- PostgreSQL; same idea as DATETIME elsewhere
);

Your application, running on a server set to UTC, inserts a record:

INSERT INTO events (description, created_at)
VALUES ('User logged in', '2023-10-26 14:30:00');

So far, so good. You know it’s 2:30 PM UTC, right? But what happens when your application server’s timezone changes? Or, more realistically, when you have application servers in different data centers across the globe? Suddenly, one server inserts a timestamp thinking it’s UTC, another inserts one thinking it’s PST, and your events table becomes a meaningless jumble of numbers. You’ve effectively stored a timezone in your application’s assumption, not in the database itself. This is a recipe for disaster.

The Right Way: TIMESTAMPTZ is Your Friend

The solution is beautifully simple: use a timezone-aware timestamp type. In PostgreSQL, this is TIMESTAMP WITH TIME ZONE (or timestamptz). Don’t let the name fool you; it doesn’t actually store the timezone offset in the field. Instead, it converts the incoming timestamp to UTC for storage. When you retrieve it, it converts it back to the timezone of your current database session. This gives you a single, unambiguous, canonical point in time.

CREATE TABLE events_correct (
    id SERIAL PRIMARY KEY,
    description TEXT,
    created_at TIMESTAMPTZ -- This is the key
);

-- Insert a timestamp with a timezone offset
INSERT INTO events_correct (description, created_at)
VALUES ('User logged in', '2023-10-26 14:30:00-04');

-- Insert a timestamp and tell the session what timezone it's in
SET TIME ZONE 'America/New_York';
INSERT INTO events_correct (description, created_at)
VALUES ('User clicked button', '2023-10-26 14:30:00');

-- The database converts both to UTC for storage. When you query them,
-- they are presented in your session's timezone, but they represent the same instant.
SET TIME ZONE 'UTC';
SELECT * FROM events_correct;
-- Both events will show as 2023-10-26 18:30:00+00, because New York was UTC-4 at that time.

The magic here is that the original timezone information is irrelevant for comparison. You’re storing a moment in time. You can always render that moment in any timezone you want later.

But My ORM/Application Framework Does…

I can hear the objections already. “But my ORM makes it hard!” or “My framework always gives me a DateTime object!” This is where you put your foot down. You are the architect. The database is the single source of truth. Forcing your application to handle timezone conversion correctly 100% of the time is a fool’s errand. Let the database, which is exceptionally good at this one job, handle it.

The best practice is to ensure all your application servers run in UTC and that your database connection is set to UTC. Then, you do all timezone conversion at the presentation layer, right before you show the time to a human. The rule is simple: Store in UTC, present in the user’s local time.

The One (Rare) Exception

There is a single, narrowly-defined use case for a timezone-less timestamp type: representing a wall-clock time that is not tied to a specific moment. The classic example is “Christmas Day 2023 starts at midnight.” This is a floating time; it happens at midnight in every timezone, sequentially. It’s not a single global instant. If your business logic is explicitly about this kind of abstract, local-time-only concept, then timestamp (without time zone) might be appropriate. For 99.9% of other cases—log events, transaction times, sensor readings, scheduling future events—you need timestamptz. When in doubt, use the one that knows about time zones. Your future self, debugging a time-related bug at 2 AM, will thank you.