Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?
like image 390
user218867 Avatar asked Mar 05 '15 08:03

user218867


1 Answers

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:

  • Ignoring time zones 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.

like image 85
Erwin Brandstetter Avatar answered Sep 21 '22 20:09

Erwin Brandstetter