Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #104

Re: timestamp and time zone

Show all headers | View raw

Am 30.04.2011 13:15, schrieb Jasen Betts:
> 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)
> 
I see. So there is no automatic translation 'without time zone',
but if you ask the system to translate it to another time zone it
does so assuming local time.

Thanks, I thought it was too much work to set up tests within
different time zones...

/Str.

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