Re: Why data of timestamptz does not store value of timezone passed to it?

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.