Обсуждение: Why data of timestamptz does not store value of timezone passed to it?

Поиск
Список
Период
Сортировка

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

От
rohtodeveloper
Дата:
<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?

От
David G Johnston
Дата:
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?

От
Steve Crawford
Дата:
<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?

От
Kevin Grittner
Дата:
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



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

От
Pavel Stehule
Дата:



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

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

От
Bruce Momjian
Дата:
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?

От
"ktm@rice.edu"
Дата:
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



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

От
Tom Lane
Дата:
"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?

От
Kevin Grittner
Дата:
"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?

От
Alvaro Herrera
Дата:
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?

От
Kevin Grittner
Дата:
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?

От
Alvaro Herrera
Дата:
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?

От
Kevin Grittner
Дата:
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



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

От
Josh Berkus
Дата:
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



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

От
Tom Lane
Дата:
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



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

От
Bruce Momjian
Дата:
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. +



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

От
Craig Ringer
Дата:
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



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

От
arhipov
Дата:
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?

От
Kevin Grittner
Дата:
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



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

От
Greg Stark
Дата:
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



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

От
Tom Lane
Дата:
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?

От
David G Johnston
Дата:
On Fri, Aug 29, 2014 at 11:12 AM, Greg Stark [via PostgreSQL] <[hidden email]> wrote:
On Fri, Aug 29, 2014 at 4:03 PM, Kevin Grittner <[hidden email]> 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: 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.

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

От
Greg Stark
Дата:
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?

От
Peter Eisentraut
Дата:
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.




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

От
Gianni Ciolli
Дата:
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?

От
rohtodeveloper
Дата:
> 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