Area of misunderstanding

For some reason, the concept of "time zone" is confusing to many users and app developers. This entails the appearance of a huge number of rough edges in the case where the application needs to deal with multiple time zones. In the end, the developers are trying to formalize this logic in the form of a special code within the application, resulting in the inevitable get honored hemorrhoids with data processing.

Here are some common incorrect causes I have heard, urging people not to use the type timestamp with time zone:

the

the
• I want to store each in the format UTC;
the
• I don't want to get a few different time zones from the request;
the
• We use a special library to handle time zones;
the
• I don't want to waste disk space to store the time zone.

All these points stem from a fundamental misunderstanding of the principles of storing temporary data in the database.



Intuitively, we can assume that timestampTZ is currently stored something like:

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

That is the most time adds information about the time zone. This is not so.

Instead, all temporary data are stored as UTC values, regardless of which type was used: timestamp without time zone or timestamp with time zone. The difference is in the recording process. If the data type involves storing information about the time zone every time you save the data, they are automatically converted from the local time of the user in the UTC time. When a user requests data, they are converted from UTC to the local timezone of the user.

For example, Josh lives in California (time zone "America / Los_Angeles"). He adds this row to the table:

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

... then Bruce that lives in Philadelphia (time zone "America / New_York") requesting information, see:

user_id | 3
message | Cool Brattle!
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 | Cool Brattle!
left_at| 2011-09-28 02:17:25+02

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

Timestamp without time zone simply do not produce any transformations, assuming all temporary data belongs to the same time zone.

For most programming languages it makes sense to leave processing time data on the conscience of the PostgreSQL server, rather than relying on additional software layer. With all responsibility we can say: "Support for temporal data in Postgres is considered as the reference. And in General, more reliable and more modern than libraries for PHP, Python, or Perl“. It is also worth noting that PostgreSQL copes with the problems of the transition to summer\winter time.

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

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

the

the
• Your driver or ORM does not support time zones (although this may be an argument in favor of the new);
the
• Your code must also work with DBMS without adequate support time zones;
the
• You are going to partition (partitioning) the table at column with the date and in need of absolute values;

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

By the way, Alvaro Herrera currently working on the creation a new supplemental data type that will store the time zone of the client application that has changed data. This type will be demanded in narrow circles, and in no way is a replacement of the standard temporal type.

Article based on information from habrahabr.ru