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

Groups > comp.databases.postgresql > #100

Re: timestamp and time zone

Show all headers | View raw

On 4/29/2011 3:23 AM, M. Strobel wrote:
> Am 29.04.2011 08:45, schrieb Harry Tuttle:
>> M. Strobel, 28.04.2011 18:00:
>>> I did not quite understand the use case of timestamp with time
>>> zone.
>>>
>>> When do you need 'with time zone'? 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?
>>
>>  From the manual at:
>>
>> http://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-TIMEZONES
>>
>>
>> All timezone-aware dates and times are stored internally in UTC.
>> They are converted to local time in the zone specified by the
>> timezone configuration parameter before being displayed to the
>> client.
>
> And I can _not_ conclude from this that a date/time without
> timezone is not stored in utc, right?
>
> The server clock is set to utc.
>
> So where does the difference show up?

On Input:

TIMESTAMP '2004-10-19 10:23:54'
TIMESTAMP '2004-10-19 10:23:54+02'

"PostgreSQL never examines the content of a literal string before
determining its type, and therefore will treat both of the above as
timestamp without time zone. To ensure that a literal is treated as
timestamp with time zone, give it the correct explicit type.

"In a literal that has been determined to be timestamp without time
zone, PostgreSQL will silently ignore any time zone indication. That
is, the resulting value is derived from the date/time fields in the
input value, and is not adjusted for time zone."

On Output:

"When a timestamp with time zone value is output, it is always
converted from UTC to the current timezone zone, and displayed as
local time in that zone. To see the time in another time zone, either
change timezone or use the AT TIME ZONE construct."

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