Groups > comp.databases.postgresql > #102

Re: timestamp and time zone

From	Jasen Betts <jasen@xnet.co.nz>
Newsgroups	comp.databases.postgresql
Subject	Re: timestamp and time zone
Date	2011-04-30 11:15 +0000
Organization	Dis (not Dat) Organisation
Message-ID	<ipgr0s$rf5$1@reversiblemaps.ath.cx> (permalink)
References	<91th8nFmufU1@mid.uni-berlin.de>

Show all headers | 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