Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
On 08/29/2014 05:28 AM, Tom Lane wrote: k...@rice.edu k...@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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kgri...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
Kevin Grittner kgri...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
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.cio...@2ndquadrant.it | www.2ndquadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
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
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
Steve Crawford scrawf...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
2014-08-28 20:26 GMT+02:00 Kevin Grittner kgri...@ymail.com: Steve Crawford scrawf...@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: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
On Thu, Aug 28, 2014 at 11:26:53AM -0700, Kevin Grittner wrote: Steve Crawford scrawf...@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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
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 scrawf...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
k...@rice.edu k...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
k...@rice.edu k...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
Alvaro Herrera alvhe...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
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 Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
Alvaro Herrera alvhe...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
Kevin Grittner kgri...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to it?
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why data of timestamptz does not store value of timezone passed to 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. 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
