Search code examples
sqlpostgresqldatetimetypestimezone

Time zone storage in data type "timestamp with time zone"

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?
Solution

  • 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 as UTC timestamp, 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 it's an inherently ambiguous data type that cannot deal with DST reliably, hence its use is discouraged:

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