Path: csiph.com!x330-a1.tempe.blueboxinc.net!feeder1.hal-mli.net!weretis.net!feeder4.news.weretis.net!news.musoftware.de!wum.musoftware.de!wieslauf.sub.de!.POSTED!reversiblemaps.ath.cx!not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: comp.databases.postgresql
Subject: Re: Timestamp with Timezone (Oracle versus Postgres)
Date: 12 Jun 2011 10:32:15 GMT
Organization: Dis (not Dat) Organisation
Lines: 34
Message-ID: <it24jf$63e$1@reversiblemaps.ath.cx>
References: <77db128d-ae4c-4ab9-ad2d-62b2e885d48b@r27g2000prr.googlegroups.com>
NNTP-Posting-Host: uCzOjTEEuGrOZPumnUkuMg.user.wieslauf.sub.de
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
X-Complaints-To: usenet@wieslauf.sub.de
NNTP-Posting-Date: Sun, 12 Jun 2011 11:00:36 +0000 (UTC)
User-Agent: slrn/pre1.0.0-18 (Linux)
X-Notice: Filtered by postfilter v. 0.8.1
X-Face: ?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri*+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s;a +M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W;(T"1(739R%m0Yyyv*gkGoPA.$b,D.w:z+<'"=-lVT?6 {T?=R^:W5g|E2#EhjKCa+nt":4b}dU7GYB*HBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q;8 "9Z{peQF,w)7UjV$c|RO/mQW/NMgWfr5*$-Z%u46"/00mx-,\R'fLPe.)^
Cancel-Lock: sha1:9Pb3oeeBgfBVrDVNrcuR+LC7JMg=
Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:131

On 2011-06-10, vinu <vinu.rm@gmail.com> wrote:
> Is timestamp with timezone a ANSI SQL (or some other) standard data
> type ? 

yes, there are rules about how it behaves and as I understand it 
postgres mets all the requirements.

> I noticing a difference in the way it is implemented in
> Postgresql and Oracle. In Oracle it is possible to know the timezone
> with which the data is inserted into the table (which is the usecase
> of the data type i feel). See example (http://www.databasejournal.com/
> features/oracle/article.php/3072991/Oracle-Time-Zone.htm)

oracle displays the timezone offset, which is not the same thing as
the timezone,

> In postgresql when you insert a record, the data is converted to UTC
> and stored. When I select the record later on it shows the timestamp
> converted to the session timezone. There is no way to know that
> originally with what timezone the data was inserted in to the table.

> In this an issue with postgresql or a conscious implementation
> decision?

as far as I can tell is was a conscious decision.

> Is there an option to view the timezone with which the
> timestamp is originally stored in the table at a later point of time
> with having to add one additional column for that purpose ?

no. you need an extra column.

-- 
⚂⚃ 100% natural