Timestamp vs. Timestamp with Time Zone in Postgres: What's the Real Difference?

Timestamp Storage in Postgres: Unraveling Time Zone Mysteries

In PostgreSQL, the data types timestamp and timestamp with time zone share a common storage size of 8 bytes. However, they differ significantly in their handling of time zone information.

Timestamp: Time without Zone

The timestamp data type stores a point in time in Coordinated Universal Time (UTC) format. It does not contain any time zone information. When retrieving a timestamp, it is displayed according to the current time zone setting of the session.

Timestamp with Time Zone: Decorated UTC Storage

The timestamp with time zone data type also stores a point in time, but it does not store the time zone information directly. Instead, it acts as an offset to calculate a UTC timestamp. When retrieving a timestamp with time zone, it is converted to a timestamp value using the given time zone and then displayed accordingly.

Important Note: No Time Zone Storage

Despite its name, the timestamp with time zone data type does not store the time zone itself. This is a common misconception. The time zone is used only for display purposes, not for storage.

Avoid Time with Time Zone

While the timestamp with time zone data type is defined in the SQL standard, its use is discouraged. It is inherently ambiguous and can lead to errors when dealing with Daylight Saving Time (DST).

The above is the detailed content of Timestamp vs. Timestamp with Time Zone in Postgres: What's the Real Difference?. For more information, please follow other related articles on the PHP Chinese website!