On 2006-01-04, Emi Lu <emilu@xxxxxxxxxxxxxxx> 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
