Groups > comp.databases.postgresql > #102

Re: timestamp and time zone

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> (permalink)
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%m0YyyvgkGoPA.$b,D.w:z+<'"=-lVT?6 {T?=R^:W5g\|E2#EhjKCa+nt":4b}dU7GYBHBxn&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

Show key headers only | View raw

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

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

timestamp and time zone "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-04-28 18:00 +0200
  Re: timestamp and time zone Dog is love <michaelnewportyahoo.com@gmail.com> - 2011-04-28 10:05 -0700
    Re: timestamp and time zone "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-04-28 19:22 +0200
  Re: timestamp and time zone Harry Tuttle <OTPXDAJCSJVU@spammotel.com> - 2011-04-29 08:45 +0200
    Re: timestamp and time zone "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-04-29 12:23 +0200
      Re: timestamp and time zone D Yuniskis <not.going.to.be@seen.com> - 2011-04-29 06:02 -0700
  Re: timestamp and time zone Jasen Betts <jasen@xnet.co.nz> - 2011-04-30 11:15 +0000
    Re: timestamp and time zone Jasen Betts <jasen@xnet.co.nz> - 2011-04-30 11:26 +0000
    Re: timestamp and time zone "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2011-04-30 17:58 +0200
      Re: timestamp and time zone Jasen Betts <jasen@xnet.co.nz> - 2011-05-01 11:33 +0000

csiph-web