Re: Timestamp with and without timezone conversion confusion.

On 10/02/2013 02:53 PM, Tim Uckun wrote:
> I do think it would help to add it to the docs. The name "TIMESTAMP WITH
> TIME ZONE" clearly implies the time zone is stored in the field. One
> day there will really be a timestamp with time zone embedded in it field
> and I wonder what they will call that.
>
>

I think you are fighting the standard here:

sql92
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

pg 31
"

Therefore, datetime data types that contain time fields (TIME and
TIMESTAMP) are maintained in Universal Coordinated Time (UTC),
with
an explicit or implied time zone part.

The time zone part is an interval specifying the difference
between
UTC and the actual date and time in the time zone represented by
the time or timestamp data item. The time zone displacement is
defined as

INTERVAL HOUR TO MINUTE

A TIME or TIMESTAMP that does not specify WITH TIME ZONE has
an im-
plicit time zone equal to the local time zone for the SQL-session.
The value of time represented in the data changes along with the
local time zone for the SQL-session. However, the meaning of the
time does not change because it is effectively maintained in UTC.

"

sql99

http://dbis-informatik.uibk.ac.at/files/ext/lehre/ss11/vo-ndbm/lit/ORel-SQL1999-IBM-Nelson-Mattos.pdf

pg 67
"
Coordinated universal time (UTC) used to store TIME and
TIMESTAMP values

WITH TIME ZONE can be specified

Each session has a time zone, which is used if no time zone is
explicitly specified

"
--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com