Re: Timestamp Conversion Woes Redux

On 21-Jul-05, at 8:54 PM, Tom Lane wrote:

> Oliver Jowett <oliver@opencloud.com> writes:
>
>> i.e. currently the core of the problem is this behaviour:
>>
>
>
>> template1=# select '2005-01-01 15:00:00
>> +1000'::timestamptz::timestamp;
>>       timestamp
>> ---------------------
>>  2005-01-01 18:00:00
>> (1 row)
>>
>
> Well, the current interpretation is that timestamptz -> timestamp
> produces a timestamp representing what the timestamptz equates to in
> your current TimeZone.  I do not foresee changing that behavior
> when/if
> we add explicit TZ info to timestamptz --- it would break just about
> every existing app that uses this conversion.
>
> In any case, this wouldn't solve Christian's complaint, because the
> root
> of his problem is that the value ever goes through timestamptz at all.
> That is necessarily going to "munge" values that don't correspond to
> legal local times in whatever zone you are using.
>
> The more I think about this, the more I think that the correct
> solution
> must include having the driver set TimeZone = UTC (and complain if the
> app tries to change it).

This really isn't an option. We can't impose limits on the
application like this.

> Then you can specify parameter types as either
> timestamp or timestamptz, it doesn't really matter, because
> conversions
> between them on the server side will be no-ops.  When you convert a
> Java
> Timestamp to send to the server, you always convert it using a UTC
> Calendar object.  I'm not sure if the setTimestamp variants with a
> Calendar are special in this regime; arguably you should ignore the
> supplied Calendar, on the principle that you know what the
> Timestamp is
> supposed to mean.
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>