Right, let’s talk about timestamptz. This is the one you actually want for almost everything, and if you’re not using it, you’re probably storing time wrong. I know that sounds direct, but it’s true. The name is a bit of a misnomer, which is the first of many quirks we’ll tackle. It doesn’t store a timezone. What it does store is a moment in time, a specific point on the universal timeline. Think of it as storing the number of seconds since a fixed point (the epoch), but with a critical, brilliant twist: it always translates to the user’s local time.

Here’s the mental model: when you insert a timestamptz value, PostgreSQL converts the provided timestamp—along with its associated timezone—into UTC. It stores that UTC moment. When you retrieve it, the database converts that stored UTC moment back into the timezone of your current database session. This is the magic. You’re storing an absolute fact (the UTC moment), while displaying a relative, context-aware one (the user’s local time).

How to Insert Data (Without Shooting Yourself in the Foot)

The key is to provide a timezone when you insert. If you don’t, it will use your session’s TimeZone setting, which is a recipe for silent, horrible data corruption. Let’s see the right way and the terrifyingly wrong way.

-- First, let's see what timezone our session is using.
-- This is CRITICAL for debugging.
SHOW TimeZone; -- Probably 'UTC' or 'US/Pacific' or something.

-- The RIGHT way: be explicit. Use an ISO 8601 string with an offset.
INSERT INTO events (event_name, occurs_at_timestamptz)
VALUES ('Database Chat', '2023-10-26 14:30:00-07');

-- Also a RIGHT way: use a named timezone.
INSERT INTO events (event_name, occurs_at_timestamptz)
VALUES ('Webinar', '2023-10-26 14:30:00 America/Los_Angeles');

-- The DANGEROUS way: using a "naive" timestamp without timezone.
-- It will assume this time is in your current session's timezone.
SET TimeZone = 'US/Pacific';
INSERT INTO events (event_name, occurs_at_timestamptz)
VALUES ('Ambiguous Meeting', '2023-10-26 14:30:00'); -- Treated as Pacific Time

SET TimeZone = 'UTC';
INSERT INTO events (event_name, occurs_at_timestamptz)
VALUES ('Another Ambiguous Meeting', '2023-10-26 14:30:00'); -- Treated as UTC!
-- These two events are now stored as DIFFERENT UTC moments. Chaos ensues.

See the problem? The string '2023-10-26 14:30:00' is meaningless on its own. It’s just numbers on a page. The -07 or America/Los_Angeles is the context that gives it meaning and allows for a perfect, unambiguous conversion to UTC.

How Retrieval Actually Works

This is where the “it doesn’t store the timezone” part becomes clear. Let’s retrieve our data while changing our session’s perspective.

SET TimeZone = 'America/New_York'; -- Eastern Time

SELECT event_name, occurs_at_timestamptz
FROM events
WHERE event_name = 'Database Chat';

-- The stored UTC moment for this event is 2023-10-26 21:30:00 UTC.
-- The database converts this to Eastern Time (which was UTC-4 in October)...
-- So it will return: 2023-10-26 17:30:00-04

The same stored UTC moment is presented differently based on the TimeZone setting of the client asking for it. This is the correct behavior for a “what time was it for the user?” display.

The Infamous “Midnight” Problem and Daylight Saving Time

This is not a PostgreSQL problem; it’s a problem with our planet’s ridiculous timekeeping politics. timestamptz handles it correctly, which sometimes feels weird. Let’s say you’re in a region that observes DST.

-- Let's try to create an event for 1:30 AM on the day DST ends in US/Eastern.
-- 2023-11-05: Clocks roll back from 2:00 AM to 1:00 AM.
-- This creates a one-hour "ambiguity" where 1:30 AM happens twice!

-- This is ambiguous. PostgreSQL will usually assume the *first* occurrence (DST).
SELECT '2023-11-05 01:30:00 America/New_York'::timestamptz;

-- But what if you need the *second* occurrence (standard time)?
-- You can't. The input is ambiguous. The designers chose one.
-- The best practice? Avoid scheduling things during ambiguous periods.
-- If you must, use UTC for that specific event to avoid any confusion.

The opposite problem, a “gap,” happens when clocks spring forward. The time from 2:00 AM to 3:00 AM might not exist. Trying to store that will simply result in the time being advanced by the appropriate amount.

Best Practices for the Trenches

  1. Use timestamptz by default. Always. For everything that represents a moment in time. Use the plain timestamp type only for things like “Christmas always happens on Dec 25th at midnight, regardless of timezone.”
  2. Configure Your Application. Your application connection pool should explicitly set the TimeZone session variable to the user’s preferred timezone (e.g., SET TimeZone = 'Europe/Paris';). This lets the database do all the heavy, correct conversion work for you, right at the source.
  3. Store in UTC, Think in Local. Your mental model should be: “I am storing UTC.” The display logic is a separate layer. timestamptz is your API between those two layers.
  4. Use Full Timezone Names (America/Los_Angeles) over Abbreviations (PST/PDT). Abbreviations are ambiguous. IST can mean India Standard Time or Irish Standard Time. The named timezones understand their own historical and future DST rules.
  5. Never, Ever Insert a Naive Timestamp. Be explicit. Always include the offset or timezone name in your input strings. This is the single biggest way to prevent corrupted data. The convenience of leaving it off is a trap laid by the database gods to test your vigilance. Don’t fall for it.