Hi,

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?

Suppose a linux server with system clock set to utc.

thanks for the light you will certainly shed on this...


/Str.

On 28 Apr., 18:00, "M. Strobel" <sorry_no_mail_h... (AT) nowhere (DOT) dee>
wrote:
some light...

http://en.wikipedia.org/wiki/Tz_database

Am 28.04.2011 19:05, schrieb Dog is love:
I've seen this light before.

The question is rather: how is timestamp stored? If it is
normalized to utc then what is the use of time zone?

/Str.

M. Strobel, 28.04.2011 18:00:
From the manual at:

http://www.postgresql.org/docs/curre...TYPE-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.

Am 29.04.2011 08:45, schrieb Harry Tuttle:
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?

/Str.

On 4/29/2011 3:23 AM, M. Strobel wrote:
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."

On 2011-04-28, M. Strobel <sorry_no_mail_here (AT) nowhere (DOT) dee> wrote:
it's best to use it when you are dealing with real events that happen
at a specific instant

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

On 2011-04-30, Jasen Betts <jasen (AT) xnet (DOT) co.nz> wrote:

It is again stored internally as a number.

As I understand it: this number is the difference in
milliseconds between "1970-01-01 00:00:00" and the time
given, for this computation both times are expressed in
a region that does not do daylight saving or leap seconds
(eg: UTC). Most linux systems use a 64 bit integer, but
some oddball ones may use a 64 bit floating point number
instead.


--
⚂⚃ 100% natural

Am 30.04.2011 13:15, schrieb Jasen Betts:
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.

On 2011-04-30, M. Strobel <sorry_no_mail_here (AT) nowhere (DOT) dee> wrote:

you can temporarily change the timezone that postres considers local
to you with the

"set timezone to " command
eg:

set timezone to "Asia/Singapore";
select now();

this makes testing other zones easy.


--
⚂⚃ 100% natural