What Exactly Lives Inside a PostgreSQL timestamp Column?

PostgreSQL maintains both timestamp and timestamptz as a single 64-bit integer: the number of microseconds since 1970-01-01 00:00:00 UTC. The distinction emerges only during data formatting for human consumption.

Why Does This Trip People Up?

• Two columns, one date… two different query results
• Your app inserts 2025-07-29 10:00, but another team sees 02:00
• The frontend renders an ISO string that doesn’t match the backend log

Two Cans of Peaches: One Plain, One Labeled

Data Type	Official Name	Stored Value	What Happens on SELECT
timestamp	timestamp without time zone	raw microsecond count	Sent back unchanged — Postgres never guesses a timezone
timestamptz	timestamp with time zone	same microsecond count	Postgres applies the session TimeZone setting just before sending the text

Analogy

• timestamp = a jar of peaches with no origin label. You know it’s fruit, but not where it was canned.
• timestamptz = a jar proudly stamped “Made in UTC+8.” Anyone opening it can decide whether to convert the nutrients panel.

Under the Hood: It’s Just a Giant Number

2000-01-01 00:00:00 UTC  → 0
2000-01-01 00:00:01 UTC  → 1 000 000

• Unit: microseconds (one-millionth of a second)
• Range: 4713 BC – 294276 AD — Indiana Jones approved
• Storage for timestamp and timestamptz is identical; interpretation differs

A 15-Second Demo

-- Client thinks in Shanghai time
SET TimeZone = 'Asia/Shanghai';

CREATE TABLE demo (
  created_ts timestamp,
  created_tz timestamptz
);

INSERT INTO demo VALUES ('2025-07-29 10:00', '2025-07-29 10:00');

Query	Result	Why
SELECT created_ts FROM demo;	2025-07-29 10:00:00	Raw value, no TZ math
SELECT created_tz FROM demo;	2025-07-29 10:00:00+08	Tag applied on output
SET TimeZone = 'UTC'; then select	2025-07-29 02:00:00+00	Same instant, new lens

Common Pitfalls & Quick Fixes

1. Different users, different clocks

• Cause: clients use different TimeZone settings with timestamptz
• Fix: either keep everything timestamp + agree on one zone, or enforce SET TimeZone = 'UTC' at connection init

2. Storing “wall time” but picked the wrong type

• Business calendars (store hours, due dates) should use timestamp
• Cross-border workflows (orders, logs) should store UTC in timestamptz

3. APIs that drift

• Always ship timestamptz as ISO-8601 strings with the offset (Z or +08:00)
• Let the UI format locally

Cheat Sheet: Which One Should I Use?

Local calendar only → timestamp
Anything global    → timestamptz (store UTC)

• Financial reports, class schedules → timestamp
• Audit logs, e-commerce orders → timestamptz

Verify in Seconds with Go Tools

Need	Tool	How-to
Inspect the epoch value from SQL	Epoch Converter	Paste 1690622400, hit Convert
Compare two timezones at a glance	Timezone Converter	Enter 10:00 Asia/Shanghai
Tidy bulk JSON with time fields	JSON Formatter	Drop in the payload, prettify & scan

All utilities run entirely in your browser — no data ever leaves your machine.

Wrap-up

• Both Postgres time types are microsecond counters; the label is the whole difference
• Choosing the wrong one means puzzling timestamps and broken math
• Test, convert, and sanity-check with the right tools to save hours of debugging