Re: TIMESTAMP vs TIMESTAMP WITHOUT TIME ZONE

Thanks for answer, I will use TIMESTAMP WITH TIMEZONE.

--
Best regards,
Julian Legeny

mailto:juleni(at)livetrade(dot)cz

=========================================================================

On Thu, Nov 10, 2005 at 04:37:36PM +0100, juleni(at)livetrade(dot)cz wrote:
> Hello,
>
> I have question about functionality for TIMESTAMP (with/without time zone).
> The main question is, what is better for usage: timemestamp WITH or WITHOUT
> time zone?
>
> I have e.g. server in USA and there is 6:00 a.m. Then I have client somwhere in
> Europe (+7 hour) and I read timestamp from server in USA.

They serve different purposes. A timestamp without timezone is for
storing times values you want to appear the same to everyone no matter
where they are. A timestamp with timezone reprentents and instant in
time and will be converted to the time appropriate for the local user.

So it all depends on what you want. You can convert between them:

my_timestamp_with_timezone AT TIME ZONE 'Europe/Amsterdam'

returns that the wallclock time of that timestamp as it was in
Amsterdam then.

The other way works too.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

=========================================================================

juleni(at)livetrade(dot)cz writes:
> I have question about functionality for TIMESTAMP (with/without time zone).
> The main question is, what is better for usage: timemestamp WITH or WITHOUT
> time zone?

You almost certainly want to store timestamp WITH time zone. It's
really poor design that the SQL spec defines plain "timestamp" to mean
the other.

regards, tom lane

=========================================================================

Thursday, November 10, 2005, 4:37:36 PM, you wrote:

jlc> Hello,

jlc> I have question about functionality for TIMESTAMP (with/without time zone).
jlc> The main question is, what is better for usage: timemestamp WITH or WITHOUT
jlc> time zone?

jlc> I have e.g. server in USA and there is 6:00 a.m. Then I have client somwhere in
jlc> Europe (+7 hour) and I read timestamp from server in USA.

jlc> Figure 1: (using pure TIMESTAMP type)
jlc> -------------------------------------
jlc> If I have defined column as:
jlc> my_date TIMESTAMP
jlc> and then I read current timestamp from the server (in USA) - it means I will use
jlc> LOCALTIMESTAMP, I will receive at the client computer current timestamp value
jlc> from USA - e.g. 10:00 a.m. But in the Europe is time 17:00 (+ 7 hour) and maybe
jlc> this can cause problems.

jlc> Figure 2: (using pure TIMESTAMP WITH TIME ZONE type)
jlc> ----------------------------------------------------
jlc> If I have defined column as:
jlc> my_date TIMESTAMP WITH TIME ZONE
jlc> and then I read current timestamp from the server (in USA) - it means I will use
jlc> CURRENT_TIMESTAMP or now(). Then I will receive at the client computer current
jlc> timestamp value from USA - e.g. 10:00 a.m. and this timestamp will be
jlc> automatically convrerted to the correct time zone (17:00 in Europe) ? It means
jlc> when I send it back from europe client to the usa server, it will be also
jlc> automatically converted to the usa timestamp?

jlc> Is that right or I have didn't understand this behaviour? Is it better in
jlc> generally to use timestamp with time zone or timestamp without time zone?

jlc> Thanks for answer,
jlc> with best regards,

jlc> Julian Legeny

jlc> mailto:juleni(at)livetrade(dot)cz