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

Комментарии

Отправить комментарий

Популярные сообщения из этого блога

Integration of PostgreSQL with MS SQL Server for those who want faster and deeper

Изображение
Recently habré was already published integration PostgreSQL and MSSQL. But, items there is categorically not enough. Therefore, the objectives of this publication are the following: the the to broaden and deepen publicly available information about FDW for MSSQL-called tds_fdw to tell the difference in major versions and describe basic compatibility issues; the to talk about the possibilities of optimization of queries that use the foreign table; the to address the subject of caching external data in a materialized view; the to say a few words about the exotic ways to integrate PostgreSQL and MSSQL. the Installing and configuring TDS FDW The guys from PostgresPro already sufficiently said about this process, I will not repeat. Will leave a few links to the official PostgreSQL documentation and examples from tds_fdw: the the creating an extension (extension) in PG: off.dokumentacija ; the server: off.documentation , example of tds_fdw ; the creating
Далее...

Custom database queries in MODx Revolution

This topic will certainly be useful for those who have quite a lot of projects on MODx Revolution, as with the usual sites of business cards is enough and the standard working methods of a La $modx- > getObject() , $modx->getCollection() , etc., and these methods are inherently not just a job database, but also with the objects of MODx. When working with large projects methods a La $modx- > getCollection() for us is not the best solution for two reasons: 1. The overexpenditures. These methods are not just getting data from DB, but also create instance of the retrieved objects. In this case, getting information about 10000 documents, we get 10000 objects of modResource that it is not very cool. 2. The task is complicated by the count of records. In addition to the direct difficulties of counting even at the query level, even if you get 10 records of the same document (for example), MODx will return as a result only one object modResource. Although often that will su
Далее...

Parse URL in Zend Framework 2

Objective: the Have a method to disassemble the components of the string containing the URL. The string can contain both absolute and relative URLS and both these options should be correctly parsed. the And in the drain, let's say the "wrong" format absolute links without "http://". Further in the text links to the "wrong" format will be called partial absolute references . the Implement support for the "RF" domain. the Example: site.ru/page.php /page.php site.ru/page.php the scheme http the host site.ru site.ru the path page.php page.php page.php Implementation of paragraphs 1 and 2 of our problem In this we should help the class Zend\Uri\Http . He has we need methods parse($uri) , getHost() , getPath() , etc. But! When we parse the URL and type " site.ru/page.php " (without the " http:// ") getHost() will return an empty string and getPath()
Далее...