Thread: Why data of timestamptz does not store value of timezone passed to it?
<div dir="ltr">Hi,all<br /> <br /> I have a question about data type "timestamp with time zone".<br />Why data of timestamptzdoes not store value of timezone passed to it?<br /><br />Considering the following example.<br /> <br /> postgres=#select '2014-08-28 14:30:30.423602+02'::timestamp with time zone;<br /> timestamptz <br />-------------------------------<br/> 2014-08-28 20:30:30.423602+08<br />(1 row)<br /> <br /> The timezone of output(+08)is different with the original input value(+02).<br />It seems not to be good behavior.But the behavior of datetype "time with time zone" is correct.<br /> <br /> postgres=# select '14:30:30.423602+02'::time with time zone;<br/> timetz <br />--------------------<br /> 14:30:30.423602+02<br />(1 row)<br /> <br /> If the correntbehavior of timestamptz is not suitable,is there any plan to correct the behavior of timestamptz or create a new datatype which can store timestamp with timezone?<br /> <br /><br />*)manual-->8.5.1.3. Time Stamps<br />---------------------------------------------------------<br/>For timestamp with time zone, the internally stored valueis always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value thathas an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zoneis stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter,and is converted to UTC using the offset for the timezone zone.<br />---------------------------------------------------------<br/> <br /> <br /> <br /> Best regarts<br /> rohto<br /> <br/> <br /> <br /> <br /> <br /> rohto<br /></div>
Re: Why data of timestamptz does not store value of timezone passed to it?
From
David G Johnston
Date:
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.
Re: Why data of timestamptz does not store value of timezone passed to it?
From
Steve Crawford
Date:
<div class="moz-cite-prefix">On 08/28/2014 01:51 AM, rohtodeveloper wrote:<br /></div><blockquote cite="mid:BAY178-W3669708526DF1D0A97C3BAC4DA0@phx.gbl"type="cite"><style><!-- .hmmessage P { margin:0px; padding:0px } body.hmmessage { font-size: 12pt; font-family:微软雅黑 } --></style><div dir="ltr">Hi,all<br /> <br /> I have a question about data type "timestamp with time zone".<br /> Why dataof timestamptz does not store value of timezone passed to it?<br /><br /> Considering the following example.<br /> <br/> postgres=# select '2014-08-28 14:30:30.423602+02'::timestamp with time zone;<br /> timestamptz <br /> -------------------------------<br /> 2014-08-28 20:30:30.423602+08<br /> (1 row)<br /> <br/> The timezone of output(+08) is different with the original input value(+02).<br /> It seems not to be good behavior.Butthe behavior of date type "time with time zone" is correct.<br /> <br /> postgres=# select '14:30:30.423602+02'::timewith time zone;<br /> timetz <br /> --------------------<br /> 14:30:30.423602+02<br/> (1 row)<br /> <br /> If the corrent behavior of timestamptz is not suitable,is there any plan tocorrect the behavior of timestamptz or create a new data type which can store timestamp with timezone?<br /> <br /><br/> *)manual-->8.5.1.3. Time Stamps<br /> ---------------------------------------------------------<br /> For timestampwith time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known asGreenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriateoffset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the timezone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.<br/> ---------------------------------------------------------<br /><br /></div></blockquote> This is actually moreappropriate for the "General" mailing list. But...<br /><br /> I have always considered "timestamp with time zone" tobe a bad description of that data type but it appears to be a carryover from the specs. It is really a "point in time"with "2014-08-28 14:30:30.423602+02" and "2014-08-28 20:30:30.423602+08" merely being different representations of thatsame point in time. "Time with time zone" is a similarly bad name as it is really a "time with offset from GMT."<br /><br/> It should be noted that -08, +02 etc. are actually *offsets* from GMT and are not, technically, time-zones. A time-zoneincludes additional information about the dates on which that offset changes due to daylight saving schedules andpolitically imposed changes thereto.<br /><br /> As the manual states, "The type <tt class="TYPE">time with time zone</tt>is defined by the SQL standard, but the definition exhibits properties which lead to questionable usefulness." Fromthe above, you can infer that one of those issues is that the offset changes based on the date but there is no date ina time with time zone field. Among the things you will discover is that '12:34:56-04' is legal input for time with timezone but '12:34:56 America/New_York' is not because you can't determine the offset without a date. Adding a date like'2014-08-28 12:34:56 America/New_York' will give you a time with offset or what the spec calls "time with time zone"(12:45:31.899075-04) though it really doesn't have any information about America/New_York.<br /><br /> That the internalrepresentation is in GMT is a curiosity but ultimately irrelevant as is it up to PostgreSQL to appropriately convert/displaywhatever it stores internally to the input and output format specified by the user.<br /><br /> The varyingvalues of things like day, month and year combined with constantly shifting definitions of time-zones make date andtime handling, *um* "interesting." Is the interval 1-day shorthand for 24-hours or the same time of day the followingday (i.e. when crossing DST boundaries). What is the appropriate value of March 31 minus one month? February 29plus one year?<br /><br /> Read and experiment to understand the quirks and the design-decisions implemented in PostgreSQL(or other program).<br /><br /> Cheers,<br /> Steve
Re: Why data of timestamptz does not store value of timezone passed to it?
From
Kevin Grittner
Date:
Steve Crawford <scrawford@pinpointresearch.com> wrote: > I have always considered "timestamp with time zone" to be a bad > description of that data type but it appears to be a carryover > from the specs. It is really a "point in time" I agree. While what timestamptz implements is a very useful data type, I think it was a very unfortunate decision to implement that for the standard type name, instead of something more consistent with the spec. It seems very unlikely to change, though, because so much existing production code would break. :-( Understandably, people do tend to expect that saving something into a column defined as TIMESTAMP WITH TIME ZONE will save a time zone with the timestamp, and in PostgreSQL it does not. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
2014-08-28 20:26 GMT+02:00 Kevin Grittner <kgrittn@ymail.com>:
Steve Crawford <scrawford@pinpointresearch.com> wrote:
> I have always considered "timestamp with time zone" to be a bad
> description of that data type but it appears to be a carryover
> from the specs. It is really a "point in time"
I agree. While what timestamptz implements is a very useful data
type, I think it was a very unfortunate decision to implement that
for the standard type name, instead of something more consistent
with the spec. It seems very unlikely to change, though, because
so much existing production code would break. :-(
Understandably, people do tend to expect that saving something into
a column defined as TIMESTAMP WITH TIME ZONE will save a time zone
with the timestamp, and in PostgreSQL it does not.
Yes, it strange for first moment, and it is difficult for beginners - but it works well .. after you switch to different mode.
But can we implement a Time Zone as special type? This and examples and documentation can better explain what it does.
Pavel
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Aug 28, 2014 at 11:26:53AM -0700, Kevin Grittner wrote: > Steve Crawford <scrawford@pinpointresearch.com> wrote: > > > I have always considered "timestamp with time zone" to be a bad > > description of that data type but it appears to be a carryover > > from the specs. It is really a "point in time" > > I agree. While what timestamptz implements is a very useful data > type, I think it was a very unfortunate decision to implement that > for the standard type name, instead of something more consistent > with the spec. It seems very unlikely to change, though, because > so much existing production code would break. :-( > > Understandably, people do tend to expect that saving something into > a column defined as TIMESTAMP WITH TIME ZONE will save a time zone > with the timestamp, and in PostgreSQL it does not. So the standard requires storing of original timezone in the data type? I was not aware of that. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Re: Why data of timestamptz does not store value of timezone passed to it?
From
"ktm@rice.edu"
Date:
On Thu, Aug 28, 2014 at 03:33:56PM -0400, Bruce Momjian wrote: > On Thu, Aug 28, 2014 at 11:26:53AM -0700, Kevin Grittner wrote: > > Steve Crawford <scrawford@pinpointresearch.com> wrote: > > > > > I have always considered "timestamp with time zone" to be a bad > > > description of that data type but it appears to be a carryover > > > from the specs. It is really a "point in time" > > > > I agree. While what timestamptz implements is a very useful data > > type, I think it was a very unfortunate decision to implement that > > for the standard type name, instead of something more consistent > > with the spec. It seems very unlikely to change, though, because > > so much existing production code would break. :-( > > > > Understandably, people do tend to expect that saving something into > > a column defined as TIMESTAMP WITH TIME ZONE will save a time zone > > with the timestamp, and in PostgreSQL it does not. > > So the standard requires storing of original timezone in the data type? > I was not aware of that. > I do not have a copy of the SQL 92 spec, but several references to the spec mention that it defined the "time zone" as a format "SHH:MM" where S represents the sign (+ or -), which seems to be what PostgreSQL uses. Regards, Ken
"ktm@rice.edu" <ktm@rice.edu> writes: > On Thu, Aug 28, 2014 at 03:33:56PM -0400, Bruce Momjian wrote: >> So the standard requires storing of original timezone in the data type? >> I was not aware of that. > I do not have a copy of the SQL 92 spec, but several references to the > spec mention that it defined the "time zone" as a format "SHH:MM" where > S represents the sign (+ or -), which seems to be what PostgreSQL uses. Yeah, the spec envisions timezone as being a separate numeric field (ie, a numeric GMT offset) within a timestamp with time zone. One of the ways in which the spec's design is rather broken is that there's no concept of real-world time zones with varying DST rules. Anyway, I agree with the upthread comments that it'd have been better if we'd used some other name for this datatype, and also that it's at least ten years too late to revisit the choice :-(. regards, tom lane
Re: Why data of timestamptz does not store value of timezone passed to it?
From
Kevin Grittner
Date:
"ktm@rice.edu" <ktm@rice.edu> wrote: > On Thu, Aug 28, 2014 at 03:33:56PM -0400, Bruce Momjian wrote: >> So the standard requires storing of original timezone in the >> data type? I was not aware of that. > > I do not have a copy of the SQL 92 spec, but several references > to the spec mention that it defined the "time zone" as a format > "SHH:MM" where S represents the sign (+ or -), which seems to be > what PostgreSQL uses. I just took a quick look at the spec to refresh my memory, and it seems to require that the WITH TIME ZONE types store UTC (I suppose for fast comparisons), it requires the time zone in the form of a hour:minute offset to be stored with it, so you can determine the local time from which it was derived. I concede that this is not usually useful, and am glad we have a type that behaves as timestamptz does; but occasionally a type that behaves in conformance with the spec would be useful, and it would certainly be less confusing for people who are used to the standard behavior. Basically, both store a moment in time in UTC, and display it with offset in hours and minutes; but the standard says it should show you that moment from the perspective of whoever saved it unless you ask for it in a different time zone, while PostgreSQL always shows it to you from the perspective of your client connection's time zone. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Why data of timestamptz does not store value of timezone passed to it?
From
Alvaro Herrera
Date:
Kevin Grittner wrote: > I just took a quick look at the spec to refresh my memory, and it > seems to require that the WITH TIME ZONE types store UTC (I suppose > for fast comparisons), it requires the time zone in the form of a > hour:minute offset to be stored with it, so you can determine the > local time from which it was derived. I concede that this is not > usually useful, and am glad we have a type that behaves as > timestamptz does; but occasionally a type that behaves in > conformance with the spec would be useful, and it would certainly > be less confusing for people who are used to the standard behavior. I remember we tried to implement this some years ago (IIRC alongside Alexey Klyukin who might remember more details). I couldn't find the thread, but one of the first problems we encountered was that we wanted to avoid storing the text name of the timezone on each datum; we had the idea of creating a catalog to attach an OID to each timezone, but that turned very quickly into a horrid mess and we discarded the idea. (For instance: if a new timezone is added in a new tzdata release, it needs to be added to the catalog, but how do you do that in minor releases?) -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: Why data of timestamptz does not store value of timezone passed to it?
From
Kevin Grittner
Date:
Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Kevin Grittner wrote: > >> I just took a quick look at the spec to refresh my memory, and it >> seems to require that the WITH TIME ZONE types store UTC (I suppose >> for fast comparisons), it requires the time zone in the form of a >> hour:minute offset to be stored with it, so you can determine the >> local time from which it was derived. I concede that this is not >> usually useful, and am glad we have a type that behaves as >> timestamptz does; but occasionally a type that behaves in >> conformance with the spec would be useful, and it would certainly >> be less confusing for people who are used to the standard behavior. > > I remember we tried to implement this some years ago (IIRC alongside > Alexey Klyukin who might remember more details). I couldn't find the > thread, but one of the first problems we encountered was that we wanted > to avoid storing the text name of the timezone on each datum; we had the > idea of creating a catalog to attach an OID to each timezone, but that > turned very quickly into a horrid mess and we discarded the idea. > > (For instance: if a new timezone is added in a new tzdata release, it > needs to be added to the catalog, but how do you do that in minor > releases?) But the standard doesn't say anything about storing a time zone *name* or *abbreviation* -- it requires that it be stored as UTC with the *offset* (in hours and minutes). That makes it pretty close to what we have -- it's all about a difference in presentation. And as far as I can see it completely dodges the kinds of problems you're talking about. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: Why data of timestamptz does not store value of timezone passed to it?
From
Alvaro Herrera
Date:
Kevin Grittner wrote: > But the standard doesn't say anything about storing a time zone > *name* or *abbreviation* -- it requires that it be stored as UTC > with the *offset* (in hours and minutes). That makes it pretty > close to what we have -- it's all about a difference in > presentation. And as far as I can see it completely dodges the > kinds of problems you're talking about. Yeah, it does, but is it useful? -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: Why data of timestamptz does not store value of timezone passed to it?
From
Kevin Grittner
Date:
Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Kevin Grittner wrote: > >> But the standard doesn't say anything about storing a time zone >> *name* or *abbreviation* -- it requires that it be stored as UTC >> with the *offset* (in hours and minutes). That makes it pretty >> close to what we have -- it's all about a difference in >> presentation. And as far as I can see it completely dodges the >> kinds of problems you're talking about. > > Yeah, it does, but is it useful? More so than CHAR(n). It would have been beneficial to support for the same reason. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 08/28/2014 02:25 PM, Kevin Grittner wrote: > But the standard doesn't say anything about storing a time zone > *name* or *abbreviation* -- it requires that it be stored as UTC > with the *offset* (in hours and minutes). That makes it pretty > close to what we have -- it's all about a difference in > presentation. And as far as I can see it completely dodges the > kinds of problems you're talking about. Except that an offset is not a timezone. This is why the spec behavior was always academic crippleware, and why we abandoned it back in ~~7.2.It does me no good at all to know that a timestampis "offset -07:00": that could be Mountain Time, Arizona Time, or Navajo Nation time, all of which will behave differently when I add 2 months to them. Unless the only goal is to be compatible with some other DBMS, in which case ... build an extension. On the other hand, I take partial responsibility for the mess which is our data type naming. What we call timestamptz should just be "timestamp", and whether or not it converts to local timezone on retrieval should be a GUC setting. And the type we call "timestamp" shouldn't exist. Hindsight is 20/20. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
Kevin Grittner <kgrittn@ymail.com> writes: > But the standard doesn't say anything about storing a time zone > *name* or *abbreviation* -- it requires that it be stored as UTC > with the *offset* (in hours and minutes).� That makes it pretty > close to what we have -- it's all about a difference in > presentation.� And as far as I can see it completely dodges the > kinds of problems you're talking about. However, the added field creates its own semantic problems. As an example, is 2014-08-28 18:00:00-04 the same as or different from 2014-08-28 17:00:00-05? If they're different, which one is less? If they're the same, what's the point of storing the extra field? And do you really like "equal" values that behave differently, not only for I/O but for operations such as EXTRACT()? (I imagine the SQL spec gives a ruling on this issue, which I'm too lazy to look up; my point is that whatever they did, it will be the wrong thing for some use-cases.) regards, tom lane
On Thu, Aug 28, 2014 at 03:25:49PM -0700, Josh Berkus wrote: > On 08/28/2014 02:25 PM, Kevin Grittner wrote: > > But the standard doesn't say anything about storing a time zone > > *name* or *abbreviation* -- it requires that it be stored as UTC > > with the *offset* (in hours and minutes). That makes it pretty > > close to what we have -- it's all about a difference in > > presentation. And as far as I can see it completely dodges the > > kinds of problems you're talking about. > > Except that an offset is not a timezone. This is why the spec behavior > was always academic crippleware, and why we abandoned it back in ~~7.2. > It does me no good at all to know that a timestamp is "offset -07:00": > that could be Mountain Time, Arizona Time, or Navajo Nation time, all of > which will behave differently when I add 2 months to them. > > Unless the only goal is to be compatible with some other DBMS, in which > case ... build an extension. > > On the other hand, I take partial responsibility for the mess which is > our data type naming. What we call timestamptz should just be > "timestamp", and whether or not it converts to local timezone on > retrieval should be a GUC setting. And the type we call "timestamp" > shouldn't exist. Hindsight is 20/20. Well, the standard TIMESTAMP requires WITHOUT TIME ZONE, so I don't know how you would be standards-compliant without it. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On 08/29/2014 04:59 AM, Kevin Grittner wrote: > I just took a quick look at the spec to refresh my memory, and it > seems to require that the WITH TIME ZONE types store UTC (I suppose > for fast comparisons), it requires the time zone in the form of a > hour:minute offset to be stored with it, so you can determine the > local time from which it was derived. I concede that this is not > usually useful, and am glad we have a type that behaves as > timestamptz does; but occasionally a type that behaves in > conformance with the spec would be useful, and it would certainly > be less confusing for people who are used to the standard behavior. FWIW, MS SQL's DateTimeOffset data type: http://msdn.microsoft.com/en-AU/library/bb630289.aspx is much more like what I, when I was getting started, expected TIMESTAMP WITH TIME ZONE to be. We don't really have anything equivalent in PostgreSQL. The PostgreSQL implementation merits some highlighted clear explanation in the documentation, explaining the concept of a point in absolute time (the first person to mention relativity gets smacked ... oh, darn) vs a wall-clock value in local time. It should also discuss the approach of storing a (instant timestamptz, timezone text) or (instant timestampts, tzoffset smallint) tuple for when unambiguous representation is required. (I guess I just volunteered myself to write a draft of that). BTW, it might be interesting to have a validated 'timezone' data type that can store time zone names or offsets, for use in conjunction with timestamptz to store a (timestamptz, timezone) tuple. Though also complicated - whether 'EST' is Australian or USA Eastern time is GUC-dependent, and it can't just be expanded into Australia/Sydney at input time because "EST" is always +1000 while Australia/Sydney could also be EDT +1100 . I hate time zones. It'd probably have to expand abbrevs to their UTC offsets at input time. -- Craig Ringer http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services
On 08/29/2014 05:28 AM, Tom Lane wrote: > "ktm@rice.edu" <ktm@rice.edu> writes: >> On Thu, Aug 28, 2014 at 03:33:56PM -0400, Bruce Momjian wrote: >>> So the standard requires storing of original timezone in the data type? >>> I was not aware of that. >> I do not have a copy of the SQL 92 spec, but several references to the >> spec mention that it defined the "time zone" as a format "SHH:MM" where >> S represents the sign (+ or -), which seems to be what PostgreSQL uses. > Yeah, the spec envisions timezone as being a separate numeric field > (ie, a numeric GMT offset) within a timestamp with time zone. One of > the ways in which the spec's design is rather broken is that there's > no concept of real-world time zones with varying DST rules. > > Anyway, I agree with the upthread comments that it'd have been better > if we'd used some other name for this datatype, and also that it's > at least ten years too late to revisit the choice :-(. > > regards, tom lane > > What about an alias for timestamptz? The current name is really confusing. As for timestamp + time-zone (not just the offset) data type, it would be very useful. For example, in Java they have 5 time types: LocalDate for representing dates (date in Postgres), LocalTime for representing times (time in Postgres), LocalDateTime to represent a date with a time (timestamp in Postgres), Instant to represent a point on the time-line (timestamptz in Postgres) and ZonedDateTime that models a point on the time-line with a time-zone. Having a type for a time-zone itself would be useful as well.
Re: Why data of timestamptz does not store value of timezone passed to it?
From
Kevin Grittner
Date:
Tom Lane <tgl@sss.pgh.pa.us> wrote: > Kevin Grittner <kgrittn@ymail.com> writes: > >> But the standard doesn't say anything about storing a time zone >> *name* or *abbreviation* -- it requires that it be stored as UTC >> with the *offset* (in hours and minutes). That makes it pretty >> close to what we have -- it's all about a difference in >> presentation. And as far as I can see it completely dodges the >> kinds of problems you're talking about. > > However, the added field creates its own semantic problems. > As an example, is 2014-08-28 18:00:00-04 the same as or different from > 2014-08-28 17:00:00-05? If they're different, which one is less? > If they're the same, what's the point of storing the extra field? > And do you really like "equal" values that behave differently, > not only for I/O but for operations such as EXTRACT()? > > (I imagine the SQL spec gives a ruling on this issue, which > I'm too lazy to look up; my point is that whatever they did, it > will be the wrong thing for some use-cases.) I think (based on your earlier post) that we agree that would have been better to implement the type named in the standard according to the definition given in the standard (and to use a new type name for the more generally useful behavior PostgreSQL currently uses for timestamptz), but that it's too late to go there now. QUEL's relational calculus is superior in just about every way to SQL, but if we're going to go with the standard because it *is* a standard, then let's freaking *do* it and extend where beneficial. Otherwise, why switch from QUEL in the first place? 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. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Fri, Aug 29, 2014 at 4:03 PM, Kevin Grittner <kgrittn@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
Kevin Grittner <kgrittn@ymail.com> writes: > It was actually rather disappointing to hear that we had a > conforming implementation and changed away from it circa the 7.2 > release; That is not the case. The existing implementation is work that Tom Lockhart did around 6.3 or so. It was called timestamp at the time, and was renamed to timestamp with time zone in 7.2, in order to make room for timestamp without time zone (which I think *is* spec compliant or close enough). That was probably an unfortunate choice; but at no time was there code in PG that did what the spec says timestamp with time zone should do. regards, tom lane
Re: Why data of timestamptz does not store value of timezone passed to it?
From
David G Johnston
Date:
On Fri, Aug 29, 2014 at 4:03 PM, Kevin Grittner <[hidden email]> wrote:I don't think the behaviour was standards-compliant in 7.2 either. For
> 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.
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: Re: Why data of timestamptz does not store value of timezone passed to it?
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Fri, Aug 29, 2014 at 4:19 PM, David G Johnston <david.g.johnston@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
Re: Re: Why data of timestamptz does not store value of timezone passed to it?
From
Peter Eisentraut
Date:
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.
Hi Craig, On Fri, Aug 29, 2014 at 10:17:17AM +0800, Craig Ringer wrote: > (...) It should also discuss the approach of storing a (instant > timestamptz, timezone text) or (instant timestampts, tzoffset > smallint) tuple for when unambiguous representation is required. > > (I guess I just volunteered myself to write a draft of that). Please notice that smallint is too small for tzoffset: SELECT d AT TIME ZONE 'Europe/Berlin' - d AT TIME ZONE 'Europe/Paris' FROM ( VALUES (date '1815-10-31') , (date'1897-02-19') ) AS f(d); Cheers, Dr. Gianni Ciolli - 2ndQuadrant Italia PostgreSQL Training, Services and Support gianni.ciolli@2ndquadrant.it | www.2ndquadrant.it
Re: Why data of timestamptz does not store value of timezone passed to it?
From
rohtodeveloper
Date:
> On 08/29/2014 04:59 AM, Kevin Grittner wrote: >> I just took a quick look at the spec to refresh my memory, and it >> seems to require that the WITH TIME ZONE types store UTC (I suppose >> for fast comparisons), it requires the time zone in the form of a >> hour:minute offset to be stored with it, so you can determine the >> local time from which it was derived. I concede that this is not >> usually useful, and am glad we have a type that behaves as >> timestamptz does; but occasionally a type that behaves in >> conformance with the spec would be useful, and it would certainly >> be less confusing for people who are used to the standard behavior. > > FWIW, MS SQL's DateTimeOffset data type: > > http://msdn.microsoft.com/en-AU/library/bb630289.aspx > > is much more like what I, when I was getting started, expected TIMESTAMP > WITH TIME ZONE to be. We don't really have anything equivalent in > PostgreSQL. > That's also what i expect,a timestamptz = timestampt + offset . Just like the current implementation of TIME WITH TIME ZONE. typedef struct {TimeADT time; /* all time units other than months and years */int32 zone; /* numeric time zone, in seconds */ } TimeTzADT; And, it's inconvenient for client(jdbc,npgsql...) to understand a strict 'timezone' (such as 'America/New_York') which comesfrom PostgreSQL and transform it to theirown data type(Such as DateTimeOffset in .NET). But a *offset* is easy to parseand process. Beast Regards rohto