Re: 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