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

Hi,all

I have a question about data type "timestamp with time zone".
Why data of timestamptz does not store value of timezone passed to it?

Considering the following example.

postgres=# select '2014-08-28 14:30:30.423602+02'::timestamp with time zone;
timestamptz
-------------------------------
2014-08-28 20:30:30.423602+08
(1 row)

The timezone of output(+08) is different with the original input value(+02).
It seems not to be good behavior.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)

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?

*）manual-->8.5.1.3. Time Stamps
---------------------------------------------------------
For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is 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.
---------------------------------------------------------

Best regarts

rohto

rohto

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.

On 08/28/2014 01:51 AM, rohtodeveloper wrote:
> Hi,all
>
> I have a question about data type "timestamp with time zone".
> Why data of timestamptz does not store value of timezone passed to it?
>
> Considering the following example.
>
> postgres=# select '2014-08-28 14:30:30.423602+02'::timestamp with time
> zone;
> timestamptz
> -------------------------------
> 2014-08-28 20:30:30.423602+08
> (1 row)
>
> The timezone of output(+08) is different with the original input
> value(+02).
> It seems not to be good behavior.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)
>
> 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?
>
>
> *）manual-->8.5.1.3. Time Stamps
> ---------------------------------------------------------
> For timestamp with time zone, the internally stored value is always in
> UTC (Universal Coordinated Time, traditionally known as Greenwich Mean
> Time, GMT). An input value that has an explicit time zone specified is
> converted to UTC using the appropriate offset for that time zone. If
> no time zone is 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.
> ---------------------------------------------------------
>
This is actually more appropriate for the "General" mailing list. But...

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" with "2014-08-28
14:30:30.423602+02" and "2014-08-28 20:30:30.423602+08" merely being
different representations of that same point in time. "Time with time
zone" is a similarly bad name as it is really a "time with offset from GMT."

It should be noted that -08, +02 etc. are actually *offsets* from GMT
and are not, technically, time-zones. A time-zone includes additional
information about the dates on which that offset changes due to daylight
saving schedules and politically imposed changes thereto.

As the manual states, "The type time with time zone is defined by the
SQL standard, but the definition exhibits properties which lead to
questionable usefulness." From the above, you can infer that one of
those issues is that the offset changes based on the date but there is
no date in a time with time zone field. Among the things you will
discover is that '12:34:56-04' is legal input for time with time zone
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.

That the internal representation is in GMT is a curiosity but ultimately
irrelevant as is it up to PostgreSQL to appropriately convert/display
whatever it stores internally to the input and output format specified
by the user.

The varying values of things like day, month and year combined with
constantly shifting definitions of time-zones make date and time
handling, *um* "interesting." Is the interval 1-day shorthand for
24-hours or the same time of day the following day (i.e. when crossing
DST boundaries). What is the appropriate value of March 31 minus one
month? February 29 plus one year?

Read and experiment to understand the quirks and the design-decisions
implemented in PostgreSQL (or other program).

Cheers,
Steve

Steve Crawford <scrawford(at)pinpointresearch(dot)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(at)ymail(dot)com>:

> Steve Crawford <scrawford(at)pinpointresearch(dot)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(at)postgresql(dot)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(at)pinpointresearch(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

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(at)pinpointresearch(dot)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(at)rice(dot)edu" <ktm(at)rice(dot)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

"ktm(at)rice(dot)edu" <ktm(at)rice(dot)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

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

Alvaro Herrera <alvherre(at)2ndquadrant(dot)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

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

Alvaro Herrera <alvherre(at)2ndquadrant(dot)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 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.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

Kevin Grittner <kgrittn(at)ymail(dot)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(at)momjian(dot)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(at)rice(dot)edu" <ktm(at)rice(dot)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.

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Kevin Grittner <kgrittn(at)ymail(dot)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(at)ymail(dot)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(at)ymail(dot)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

On Fri, Aug 29, 2014 at 11:12 AM, Greg Stark [via PostgreSQL] <
ml-node+s1045698n5816903h17(at)n5(dot)nabble(dot)com> wrote:

> On Fri, Aug 29, 2014 at 4:03 PM, Kevin Grittner <[hidden email]
> <http://user/SendEmail.jtp?type=node&node=5816903&i=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.

On Fri, Aug 29, 2014 at 4:19 PM, David G Johnston
<david(dot)g(dot)johnston(at)gmail(dot)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

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(dot)ciolli(at)2ndquadrant(dot)it | www.2ndquadrant.it

> 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 comes from PostgreSQL and transform it to theirown data type(Such as DateTimeOffset in .NET). But a *offset* is easy to parse and process.

Beast Regards
rohto