Timestamp vs Timestamptz in PostgreSQL: Key Differences and Usage

In PostgreSQL, timestamp stores date and time without time zone information, while timestamptz stores date and time with time zone awareness, automatically converting to UTC internally. Use timestamptz when you need consistent time across time zones, and timestamp when time zone is not relevant.

Quick Comparison

This table summarizes the main differences between timestamp and timestamptz in PostgreSQL.

Feature	timestamp	timestamptz
Time zone storage	No time zone stored	Does not store time zone, converts to UTC internally
Input interpretation	Takes input as-is	Converts input to UTC based on time zone
Output display	Shows stored value as-is	Converts UTC to session time zone on output
Use case	When time zone is irrelevant	When time zone consistency matters
Storage size	8 bytes	8 bytes
Comparison behavior	Simple byte comparison	Normalized to UTC before comparison

Key Differences

The timestamp type in PostgreSQL stores only the date and time without any time zone information. This means it records the exact value you provide and returns it exactly as stored, regardless of the user's or server's time zone settings. It is useful when you want to store local times or when time zones do not matter, such as scheduling events in a fixed local time.

On the other hand, timestamptz (timestamp with time zone) stores the date and time along with time zone awareness. Internally, PostgreSQL converts the input time to Coordinated Universal Time (UTC) and stores it. When you query the data, it converts the UTC time back to the time zone of the current database session, ensuring consistent interpretation across different time zones.

Because of this, timestamptz is preferred for applications that operate across multiple time zones or need to track absolute points in time, such as logging events or coordinating schedules globally. The storage size for both types is the same, but their behavior in input, storage, and output differs significantly.

Code Comparison

sql

CREATE TABLE example_timestamp (
  event_time timestamp
);

INSERT INTO example_timestamp (event_time) VALUES ('2024-06-01 12:00:00');

SELECT event_time FROM example_timestamp;

Output

event_time
---------------------
2024-06-01 12:00:00
(1 row)

timestamptz Equivalent

sql

CREATE TABLE example_timestamptz (
  event_time timestamptz
);

INSERT INTO example_timestamptz (event_time) VALUES ('2024-06-01 12:00:00+02');

SELECT event_time FROM example_timestamptz;

Output

event_time
----------------------------
2024-06-01 10:00:00+00
(1 row)

When to Use Which

Choose timestamp when you want to store date and time exactly as given, without any time zone conversion, such as for local schedules or fixed-time events.

Choose timestamptz when you need to track moments in absolute time across different time zones, like logging, event tracking, or applications used globally. It ensures consistent time interpretation regardless of user or server location.

Key Takeaways

timestamp stores date and time without time zone, showing values as entered.

timestamptz stores date and time with time zone, converting to UTC internally.

Use timestamptz for global or multi-time zone applications.

Use timestamp for local time storage where time zone is irrelevant.

Both types use 8 bytes of storage but behave differently in input and output.