When it is better to use "timestamp without time zone"?

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