From: | Andrew - Supernews <andrew+nonews(at)supernews(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: When it is better to use "timestamp without time zone"? |
Date: | 2006-01-04 16:40:37 |
Message-ID: | slrndrnuk5.2iki.andrew+nonews@atlantis.supernews.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2006-01-04, Emi Lu <emilu(at)cs(dot)concordia(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | MargaretGillon | 2006-01-04 17:23:19 | Re: Visual FoxPro 9 ODBC errors |
Previous Message | Jaime Casanova | 2006-01-04 15:36:19 | Re: Unique transaction ID |