What Exactly Lives Inside a PostgreSQL timestamp Column?

What Exactly Lives Inside a PostgreSQL timestamp Column?

TL;DR PostgreSQL keeps both timestamp and timestamptz as a single 64‑bit integer: the number of microseconds since 1970‑01‑01 00:00:00 UTC. The magic happens only when the data is formatted for humans.

---

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.

If any of that sounds familiar, this post is for you. We’ll translate the jargon, use food analogies, walk through a five‑line SQL demo, and finish with a one‑click sanity check using Go Tools’ Time converters.

---

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.

Curious? Pop open Go Tools → Epoch Converter, paste any date, and see the raw microseconds—or do the reverse.

---

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.

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.

Go Tools → Timezone Converter will instantly show how the same moment looks from New York, London, etc. Paste and visually confirm before pushing code.

---

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.
• With Go Tools you can test, convert, and sanity‑check in three clicks.

So next time someone asks, “What’s really stored in that pg timestamp column?”—send them this cheat‑sheet and our converters. You’ll save a meeting, maybe even a sprint.