Path: csiph.com!x330-a1.tempe.blueboxinc.net!aioe.org!wieslauf.sub.de!.POSTED!reversiblemaps.ath.cx!not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: comp.databases.postgresql
Subject: Re: timestamp and time zone
Date: 30 Apr 2011 11:15:40 GMT
Organization: Dis (not Dat) Organisation
Lines: 34
Message-ID: <ipgr0s$rf5$1@reversiblemaps.ath.cx>
References: <91th8nFmufU1@mid.uni-berlin.de>
NNTP-Posting-Host: Wk8PyTvLHfx9YaNov58aSw.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: Sat, 30 Apr 2011 11:30:35 +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:VBfjjcNNUlIMQ1yxtCezfipt/Zw=
Xref: x330-a1.tempe.blueboxinc.net comp.databases.postgresql:102

On 2011-04-28, M. Strobel <sorry_no_mail_here@nowhere.dee> wrote:
> Hi,
>
> I did not quite understand the use case of timestamp with time zone.
>
> When do you need 'with time zone'?

it's best to use it when you are dealing with real events that happen
at a specific instant

> My tests show you can
> calculate other time zones time easily from a timestamp without
> time zone.

> Is timestamp internally stored normalized to utc, and calculated
> to client time zone? Or to server time zone?

timestamp with timezone is internally stored as a UTC timestamp
no zone information is stored. So '2011-04-30 23:02:39.296282+12'
is actually stored as '2011-04-30 11:02:39.296282 UTC'.  (I know 
it says "with timezone" on the label - postgresql fakes it )
(actually it's stored as a number, not as a string)

When it's retreived it will be translated to the best guess zone
apropriate for the user (using the server operating system's timezone
database)

timestamp is internally stored with no zone translation so 
'2011-04-30 23:02:39.296282' will look the same whatever 
the user uses for their timezone. (different datestyle
settings can effect the date part however)

-- 
⚂⚃ 100% natural