[HACKERS] Re: Why data of timestamptz does not store value of timezone passed to it?
On Fri, Aug 29, 2014 at 4:03 PM, Kevin Grittner kgri...@ymail.com wrote: It was actually rather disappointing to hear that we had a conforming implementation and changed away from it circa the 7.2 release; and even more disturbing to hear that decision is still being defended on the grounds that there's no point providing standard conforming behavior if we can think of different behavior that we feel is more useful. We should have both. I don't think the behaviour was standards-compliant in 7.2 either. For that matter, I can't think of any circumstance where the standard behaviour is useful. There's absolutely no way to write correct code using it. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Why data of timestamptz does not store value of timezone passed to it?
On Fri, Aug 29, 2014 at 11:12 AM, Greg Stark [via PostgreSQL] ml-node+s1045698n5816903...@n5.nabble.com wrote: On Fri, Aug 29, 2014 at 4:03 PM, Kevin Grittner [hidden email] http://user/SendEmail.jtp?type=nodenode=5816903i=0 wrote: It was actually rather disappointing to hear that we had a conforming implementation and changed away from it circa the 7.2 release; and even more disturbing to hear that decision is still being defended on the grounds that there's no point providing standard conforming behavior if we can think of different behavior that we feel is more useful. We should have both. I don't think the behaviour was standards-compliant in 7.2 either. For that matter, I can't think of any circumstance where the standard behaviour is useful. There's absolutely no way to write correct code using it. And forcing people to change their data types to migrate to PostgreSQL is undesirable IF our type is usefully equivalent to others in the majority of situations - though I don't know if that is actually the case. 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-tp5816703p5816906.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
[HACKERS] Re: Why data of timestamptz does not store value of timezone passed to it?
On Fri, Aug 29, 2014 at 4:19 PM, David G Johnston david.g.johns...@gmail.com wrote: And forcing people to change their data types to migrate to PostgreSQL is undesirable IF our type is usefully equivalent to others in the majority of situations - though I don't know if that is actually the case. You know... I wonder if we have enough leverage in the standards committee these days that we could usefully push that direction instead of being pushed around. The standard timestamp with time zone is not very useful and I'm sure the standards committee wouldn't mind having a useful point-in-time data type. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Why data of timestamptz does not store value of timezone passed to it?
On 8/29/14 11:27 AM, Greg Stark wrote: You know... I wonder if we have enough leverage in the standards committee these days that we could usefully push that direction instead of being pushed around. The standard timestamp with time zone is not very useful and I'm sure the standards committee wouldn't mind having a useful point-in-time data type. Not likely unless Oracle or IBM have an existing implementation. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers