Time zone storage in data type “timestamp with tim

Question

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?

Answer 1

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, which is actually stored. 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:

• Ignoring timezones altogether in Rails and PostgreSQL

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.

Answer 2

Looking at the documentation:

• Timestamps are stored either as integers, or (deprecated) floating point numbers
• I don't believe timestamp with timezone could be correctly encoded within 8 bytes if it actually stored a time zone. Just the timestamp requires 64 bits, as log₂(298989 * 365 * 24 * 60 * 60 * 1000000) is greater than 63. Note that time with time zone requires 12 bytes, with the same precision but a range of a single day.

See Erwin's answer to explain how it actually manages to be stored in 8 bytes - it should be called "timestamp without a time zone, but stored in UTC and converted into the local time zone for display". Ick.