Обсуждение: When it is better to use "timestamp without time zone"?
Greetings, Happy New Year listing! I have a question about column type timestamp "with time zone" and "without time zone". . when data are import/export into different time zones, column timestamp with time zone is necessary For example, "2005-01-01 2001:01:01+05" under timezone1 will still be saved as "2005-01-01 2001:01:01+05" under different time zones Otherwise, "2005-01-01 2001:01:01" means different timestamp under two different time zones I need to know when it is better that we use "timestamp without time zone" ? And when it is better to use "timestamp with time zone"? Thanks a lot! Emi
On Jan 3, 2006, at 16:01 , Emi Lu wrote: > I need to know when it is better that we use "timestamp without > time zone" ? And when it is better to use "timestamp with time zone"? In my opinion, if you want to store a timestamp, you should always use timestamp with time zone, as the UTC offset information is necessary to provide a unique timestamp value. Michael Glaesemann grzm myrealbox com
>> I need to know when it is better that we use "timestamp without time >> zone" ? And when it is better to use "timestamp with time zone"? > > In my opinion, if you want to store a timestamp, you should always > use timestamp with time zone, as the UTC offset information is > necessary to provide a unique timestamp value. > In postgreSQL, the default value for timestamp is "without time zone". Mysql supports the format as "2005-01-01 12:01:01" but not "2005-01-01 12:01:01+05" at the moment. Can I say when data is used among diff time zones, timestamp with time zone is a MUST; otherwise, timestamp without time zone is used? Emi
On 2006-01-04, Emi Lu <emilu@cs.concordia.ca> wrote: > In postgreSQL, the default value for timestamp is "without time zone". That's true only because the SQL standard says so, not because it's actually a good idea to use timestamps without time zone. > Can I say when data is used among diff time zones, timestamp with time > zone is a MUST; otherwise, timestamp without time zone is used? No. You should, IMO, use timestamp with time zone in essentially all cases. (In particular, you should _always_ use it for recording the time at which an event happened, which covers most uses of timestamps.) Only use timestamp without time zone for data storage if you have a specific reason to do so. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
>>In postgreSQL, the default value for timestamp is "without time zone". >> >> > >That's true only because the SQL standard says so, not because it's actually >a good idea to use timestamps without time zone. > > >>Can I say when data is used among diff time zones, timestamp with time >>zone is a MUST; otherwise, timestamp without time zone is used? >> >> > >No. You should, IMO, use timestamp with time zone in essentially all cases. >(In particular, you should _always_ use it for recording the time at which >an event happened, which covers most uses of timestamps.) Only use timestamp >without time zone for data storage if you have a specific reason to do so. > > 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? 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" ? . 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? . 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. Emi
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