Storing the time zone in the time stamp with time zone data type

Question

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

My questions:

• What format is used to store date and time in a timestamp?
• How is timestamp with timezone information stored in a timestamp with timezone type and how is it analyzed later when reading the type?

Answer 1

This is simply a misunderstanding related to a somewhat misleading type name. The time zone itself is not saved at all . It simply acts as an offset to calculate the UTC timestamp that is actually stored. All this conforms to the SQL standard.

Only a point in time is saved, no information about the zone. That is why 64 bits of information are enough. The timestamp is displayed to the client according to the setting of the current session time zone.

More details:

• Ignoring Time Zones in General in Rails and PostgreSQL

In addition, since John mentioned this, time with time zone defined in the SQL standard and thus implemented in Postgres, but its use is not recommended

time with time zone is defined by the SQL standard, but the definition demonstrates properties that lead to questionable utility.

This is an inherently ambiguous type that cannot handle DST properly.

Answer 2

Looking at the documentation :

• Timestamps are stored either as integers or (deprecated) floating point numbers
• I do not believe that timestamp with timezone can be correctly encoded within 8 bytes if it actually stores the timezone. 64 bits are required for time stamping, since log ₂ (298989 * 365 * 24 * 60 * 60 * 1,000,000) is more than 63. Note that time with time zone requires 12 bytes with the same accuracy, but the range is one day.

See Erwin's answer for an explanation of how it is actually stored in 8 bytes — it should be called a “timestamp without a time zone, but stored in UTC and converted to a local time zone for display”. Hk.