PostgreSQL timestamp vs timestamptz: What’s Actually Stored Under the Hood?

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

Timestamp Arithmetic and Intervals

One of the most practical aspects of PostgreSQL timestamps is interval arithmetic. Because both types store microsecond counts, you can add and subtract intervals directly:

-- Add 3 hours and 30 minutes
SELECT '2025-07-29 10:00'::timestamptz + INTERVAL '3 hours 30 minutes';
-- → 2025-07-29 13:30:00+08

-- Find the difference between two timestamps
SELECT '2025-07-30 09:00'::timestamptz - '2025-07-29 10:00'::timestamptz;
-- → 23:00:00  (an interval)

-- Extract specific fields
SELECT EXTRACT(EPOCH FROM '2025-07-29 10:00:00+08'::timestamptz);
-- → 1753768800  (Unix timestamp in seconds)

-- Truncate to day boundary (useful for daily aggregations)
SELECT date_trunc('day', '2025-07-29 15:42:19+08'::timestamptz);
-- → 2025-07-29 00:00:00+08

The EXTRACT(EPOCH FROM ...) function is particularly useful when you need to pass timestamps to external systems that expect Unix epoch seconds. Conversely, you can convert an epoch back to a timestamp:

SELECT to_timestamp(1753768800);
-- → 2025-07-29 10:00:00+08  (in Asia/Shanghai session)

A subtle but important point: interval arithmetic with timestamp (without timezone) ignores DST transitions entirely, while timestamptz respects them. This means adding INTERVAL '1 day' to a timestamptz value that crosses a DST boundary will correctly return the same wall-clock time — not exactly 24 hours later.

Indexing and Performance Considerations

Both timestamp and timestamptz are stored as 8-byte integers, so there is no performance difference between them for storage or indexing. B-tree indexes work identically on both types because the underlying comparison is just integer comparison.

However, there are a few practical considerations:

Range queries: WHERE created_at > '2025-07-01' works efficiently with an index on either type. With timestamptz, PostgreSQL converts the literal to UTC before comparison, so the index is still used.
Partition keys: When using range partitioning on timestamp columns, timestamptz is generally safer because partition boundaries are unambiguous (always UTC). With timestamp, a boundary like '2025-07-01 00:00' could mean different things to different sessions.
Functional indexes: If you frequently query by date only (ignoring time), consider an index on date_trunc('day', created_at) to speed up daily aggregation queries.

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

A common pattern in application code is to set the timezone once at connection pool initialization:

-- In your connection setup (e.g., pg pool config)
SET timezone = 'UTC';

This ensures all sessions see the same UTC representation, and your application layer handles the conversion to local time for display.

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

The test is simple: if the question is “what moment in time did this happen?” use timestamptz. If the question is “what does the clock on the wall say?” use timestamp.

3. APIs that drift

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

4. Comparing timestamps across types

Mixing timestamp and timestamptz in comparisons or joins is a common source of subtle bugs:

-- Dangerous: implicit cast applies session timezone
SELECT * FROM orders o
JOIN schedules s ON o.created_tz = s.start_ts;
-- PostgreSQL casts s.start_ts to timestamptz using session timezone
-- Different sessions can get different join results!

Fix: always cast explicitly when comparing across types, or standardize on one type per domain.

5. ORM default pitfalls

Many ORMs (Django, SQLAlchemy, ActiveRecord) default to timestamp without timezone. Check your migration files — if your app serves users across timezones, override the default to timestamptz. In Django, set USE_TZ = True in settings. In SQLAlchemy, use DateTime(timezone=True).

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
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.

Frequently Asked Questions

What is the difference between timestamp and timestamptz in PostgreSQL?

timestamp (without time zone) stores a date-time value as-is, with no timezone context. timestamptz (with time zone) converts the input to UTC for storage and converts back to the session’s timezone on retrieval. Use timestamptz for almost all cases — it prevents timezone-related bugs across distributed systems.

Does PostgreSQL actually store the timezone in timestamptz?

No — despite the name, PostgreSQL does not store the timezone itself. It converts the input to UTC and stores only the UTC value (a microsecond count from 2000-01-01). On retrieval, it converts from UTC to whatever timezone your session’s timezone setting specifies. The original timezone information is discarded.

How do I change the timezone for a PostgreSQL session?

Run SET timezone = 'America/New_York'; to change the session timezone. This affects how timestamptz values are displayed and interpreted. For server-wide defaults, set timezone in postgresql.conf. Always use IANA timezone names (like Asia/Shanghai) rather than abbreviations (like CST) to avoid ambiguity.

Should I use timestamp or timestamptz for storing event times?

Use timestamptz for nearly everything — user actions, API calls, audit logs, and scheduled events. Only use timestamp (without timezone) for abstract times that aren’t tied to a specific moment, like “store opens at 09:00” which means 9 AM in whatever the local timezone is, not a specific UTC instant.

How does PostgreSQL handle daylight saving time with timestamptz?

PostgreSQL handles DST correctly when using timestamptz because it stores everything in UTC internally. When you retrieve a value, PostgreSQL converts from UTC using the current DST rules for your session timezone. This means the same stored UTC instant correctly shows different local times before and after a DST transition.

For a comprehensive guide to Unix timestamps — including precision handling, timezone best practices, and code examples in JavaScript, Python, and Go — see our Unix Timestamp Guide.

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

What Exactly Lives Inside a PostgreSQL timestamp Column?