Re: TIMESTAMP vs TIMESTAMP WITHOUT TIME ZONE - Mailing list pgsql-general

Thanks for answer, I will use TIMESTAMP WITH TIMEZONE.

--
Best regards,
Julian Legeny

mailto:juleni@livetrade.cz


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

On Thu, Nov 10, 2005 at 04:37:36PM +0100, juleni@livetrade.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@svana.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@livetrade.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@livetrade.cz