Re: Why data of timestamptz does not store value of timezone passed to it? - Mailing list pgsql-hackers

<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