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

From: rohtodeveloper <rohtodeveloper(at)outlook(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Why data of timestamptz does not store value of timezone passed to it?
Date: 2014-08-28 08:51:27
Message-ID: BAY178-W3669708526DF1D0A97C3BAC4DA0@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Etsuro Fujita 2014-08-28 09:00:04 Re: inherit support for foreign tables
Previous Message Marti Raudsepp 2014-08-28 08:48:55 Re: delta relations in AFTER triggers