For some reason, the very notion of "time zone" is confusing to many users and app developers.It entails a huge number of rough edges when applications have to deal with multiple time zones. Developers end up trying to implement this logic as special code within the application, and inevitably end up with some well-deserved data handling hassle.
Here are some common erroneous reasons I’ve heard calling for not using type timestamp with time zone :
- I want to store everything in the format UTC ;
- I don’t want to get several different time zones from the query;
- We use a special library to handle time zones;
- I don’t want to waste disk space to store the time zone.
All these theses grow out of a fundamental misunderstanding of the principles of temporal data storage in a database.
Intuitively, one would assume that 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. This is far from it.
Instead, all temporary data is stored as UTC values, no matter what type was used : timestamp without time zone or else timestamp with time zone The difference is in the writing process. If the data type involves storing time zone information, then whenever data is saved, it is automatically converted from the user’s local time to UTC time. When the user requests the data, it is converted from UTC to the user’s local time zone.
Suppose, Josh lives in California (time zone "America / Los_Angeles"). He adds such a line to the table :
INSERT INTO messages ( user_id, message, left_at )
VALUES ( 3, 'Great brattelo!', '2011-09-27 17:17:25' );
then Bruce that lives in Philadelphia (time zone "America / New_York"), requesting the data, will see :
user_id | 3
message | Great brattelo!
left_at | 2011-09-27 20:17:25-04
and Magnus who lives in Sweden ("Europe / Stockholm"), will in turn receive :
user_id | 3
message | Great brattelo!
left_at | 2011-09-28 02:17:25+02
The 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 conversion, assuming that all timestamp data belongs to the same time zone.
For most programming languages, it makes sense to leave the temporary data handling to the PostgreSQL server rather than relying on an additional programming layer. We can responsibly say: "Temporary data support in Postgres is the benchmark. And in general, it is more reliable and up-to-date than libraries for PHP, Python or Perl". It is also worth noting that PostgreSQL handles the summer/winter time transition very well.
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 parameter TIMEZONE for the user session and the time data will automatically be displayed in the corresponding time zone.
Of course, there are several good reasons not to keep time zone information :
- Your driver or ORM does not support time zones (although this could be an argument for a new one);
- Your code should also work with an RDBMS without adequate time zone support;
- You are going to partition a table by date column and need absolute values;
- The 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 type timestampTZ
By the way, Alvaro Herrera is currently is working to create a 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 is in no way a replacement for the standard temporal types.