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?
Jon Skeet
people
quotationmark

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 log2(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.

people

See more on this question at Stackoverflow