rohtodeveloper wrote > I have a question about data type "timestamp with time zone". > Why data of timestamptz does not store value of timezone passed to it? > > The timezone of output(+08) is different with the original input > value(+02). > It seems not to be good behavior.
Its good for the inumerable people who use it every day without difficulty... The why is that the goal of timestamptz is to represent a single point-in-time. For all practical purposes the introduction of timezones simply allows for multiple equivalent representations of said point. Postgres has simply chosen UTC as the canonical representation for storage purposes and uses client-provided timezone information to transform the stored valued into the equivalent representation that is thought to be most useful to the user. > But the behavior of date type "time with time zone" is correct. > > postgres=# select '14:30:30.423602+02'::time with time zone; > timetz > -------------------- > 14:30:30.423602+02 > (1 row) Inconsistent (wrt timestamptz), and possibly buggy (though doubtful, consistency is not mandatory), but the documentation itself says that "time with time zone" has problematic properties mandated by the SQL standard. The issue is that without knowing the date within a given timezone one does not know the adjustment value to use. TimeZones are inherently date dependent - so timetz is fundamentally flawed even if it can be used to good effect in limited situations. If this does what you need then create a composite type (date, timetz). Once you starting doing modifications to your custom type you will likely find the timestamptz behavior to be more useful and accurate. > If the corrent behavior of timestamptz is not suitable,is there any plan > to correct the behavior of timestamptz or create a new data type which can > store timestamp with timezone? Timestamptz will never be changed from its current behavior. The bar to introduce another timestamptz-like data type with different behavior is extremely high. It would probably be worthwhile for everyone if you share what you are actually trying to accomplish instead of just throwing out the claim that the data type is broken. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-data-of-timestamptz-does-not-store-value-of-timezone-passed-to-it-tp5816703p5816737.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers