Sql – Time zone storage in data type “timestamp with time zone”

datetimepostgresqlsqltimezonetypes

In PostgreSQL, the data types timestamp and timestamp with timezone both use 8 bytes.

My questions are:

  1. What format is used to store date & time in a timestamp?
  2. How is the time zone information stored in the timestamp with timezone
    type, and how is it parsed later when reading the type?

Best Answer

This is just a misunderstanding stemming from the somewhat misleading type name. The time zone itself is not stored at all. It just acts as offset to compute a UTC timestamp (input), which is actually stored. Or as decorator in the display of a timestamp according to the current or given time zone (output). That's all according to the SQL standard.

Just the point in time is stored, no zone information. That's why 64 bit of information is enough. The timestamp is displayed to the client according to the current time zone setting of the session.

Details:

Also, since Jon mentioned it, time with time zone is defined in the SQL standard and thus implemented in Postgres, but its use is discouraged:

time with time zone is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness.

It's an inherently ambiguous type that cannot deal with DST properly.

Related Topic