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

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

From
Emi Lu
Date:
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





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

From
Michael Glaesemann
Date:
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



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

From
Emi Lu
Date:
>> 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

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

From
Andrew - Supernews
Date:
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

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

From
Emi Lu
Date:

>>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



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

From
Andrew - Supernews
Date:
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