Misunderstanding zone

For some reason, the very concept of “time with a time zone” confuses many users and application developers. This entails the appearance of a huge number of rough edges in the case when applications must deal with a multitude of time zones. Ultimately, the developers are trying to arrange this logic in the form of a special code inside the application, as a result of which they inevitably receive well-deserved hemorrhoids with data processing.

Here are some common erroneous reasons I’ve heard urging not to use the timestamp type with time zone :

• I want to store everything in UTC ;
• I do not want to receive several different time zones from the request;
• We use a special library to handle time zones;
• I do not want to spend disk space to store the time zone.

')
All these theses grow out of a fundamental misunderstanding of the principles of storing temporary data in a database.



Intuitively, it can be assumed that the timestampTZ is currently stored approximately as:

"2011-06-11 15:53:22 PDT"

That is, the time zone information is added to the time itself. Far from it.

Instead, all time data is stored as UTC values, regardless of what type was used: timestamp without time zone or timestamp with time zone . The difference is in the recording process. If the data type implies the storage of time zone information, then whenever data is saved, it is automatically converted from the user's local time during UTC. When a user requests data, it is converted from UTC to the user's local time zone.

Suppose Josh lives in California (time zone is America / Los_Angeles). It adds such a row to the table:

INSERT INTO messages ( user_id, message, left_at )
VALUES ( 3, ' !', '2011-09-27 17:17:25' );

... then Bruce , who lives in Philadelphia (the time zone of America / New_York), when requesting data, will see:

user_id | 3
message | !
left_at | 2011-09-27 20:17:25-04

... and Magnus , who lives in Sweden ("Europe / Stockholm"), in turn, will receive:

user_id | 3
message | !
left_at | 2011-09-28 02:17:25+02

Data is stored as UTC, but what each user sees is tied to their local time.

Timestamp without time zone simply does not perform any transformations, assuming that all temporary data belong to the same time zone.

For most programming languages, it makes sense to leave the processing of temporary data on the PostgreSQL server’s conscience, rather than relying on an additional software layer. We can state with full responsibility: „The support of temporary data in Postgres is by right the reference. And in general, more reliable and modern than in libraries for PHP, Python or Perl. " It is also worth noting that PostgreSQL perfectly copes with the problems of the transition to summer / winter time.

More importantly, using timestampTZ means that you never have to worry about writing application code to display data in the user's time zone. Instead, you just need to set the TIMEZONE parameter for a user session and the temporary data will automatically be displayed in the corresponding time zone.

Of course, there are several good reasons not to store information about the time zone:

• Your driver or ORM does not support time zones (although this may be an argument in favor of a new one);
• Your code should also work with the DBMS without adequate support for time zones;
• You are about to partition the table by date column and need absolute values;
• A database will never be used in more than one time zone.

But if none of these reasons apply to you, then you should use the timestampTZ type.

By the way, Alvaro Herrera is currently working on creating a new additional data type that will allow storing the time zone of the client application that changed the data. This type will be in demand in narrow circles, and in no way is a substitute for standard temporal types.