On 2006-01-04, Emi Lu <emilu@cs.concordia.ca> wrote:
> OK. When the column is setup as "timestamp with time zone default
> now()", the default values will be set based on the Operating System,
> right?
You have to understand that in the current implementation, pg does not
actually store the time zone.
> An example case:
> PostgreSQL server is on machine1, with timezone setup as "-5". A table
> named test1(col timestamp with time zone default now() );
>
> . insert into test1 from client machine2 with timezone "+2"; the value
> inserted into machine1 should be "2006-01-04 10:01:01-05" but not
> "2006-01-04 10:01:01+02" ?
If the client gave the value as '2006-01-04 10:01:01', then the value is
taken to be in whatever the session's timezone setting is. If the client
didn't set that (either on connect, or via a SET command, or as a per-user
or per-database default) then the server's timezone is the default.
It's important in this context to note that "-5" or "+2" don't sufficiently
specify time _zones_ as opposed to _timezone offsets_. When you're talking
about a specific time, you can say '2006-01-04 10:01:01-0500', but to say
that "a machine is in timezone -5" is generally nonsense. In the real world,
you have to take into account DST rules both current and historical, which
the timezone libraries know about.
> . select * from test1 from client machine2, we will get "2006-01-04
> 10:01:01-05" since the absolute value is saved, which is never caculated
> again?
The result will be whatever the stored time is _in the session's timezone_.
> . What is the problem here when the column type is setup as "timestamp
> without time zone"?
> The value "2006-01-04 10:01:01" is saved and read from both machine1
> and machine2.
But '2006-01-04 10:01:01' doesn't mean the same thing in two different
timezones.
If what matters is that the result say "10:01:01" regardless of what
timezone the client is in, then you want timestamp without time zone. If
what matters is that the result be the _same time_ regardless of what
timezone, then you want timestamp _with_ time zone. The second case is
vastly more common.
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services