Re: Timestamp Conversion Woes Redux

I've hacked my local version to track the servers time zone and use
the calendar appropriate to the servers time zone.
This would allow the user to set the driver, and server time zone to
UTC as suggested below.

One issue I have is that the server returns some time zones in
lowercase.

ie EST -> est
     EST5EDT -> est5edt

java doesn't understand these. Why is the server changing them to
lower case?

Dave

On 21-Jul-05, at 9:57 PM, Oliver Jowett wrote:

> 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.
>>
>
> I can understand backwards compatibility issues, but it doesn't
> make my
> example above any more obvious :/
>
>
>> 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).  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.
>>
>
> I don't think this works, as we need to support the Calendar variants.
> Specifically, given this code:
>
>   // 2005-01-01 05:00:00 UTC
>   Timestamp now = new Timestamp(1104555600*1000L);
>   Calender localCalendar = Calendar.getInstance(new Locale
> ("en","NZ"));
>   ps.setTimestamp(1, now, localCalendar);
>
> I would expect it to end up setting a timestamptz value of '2005-01-01
> 05:00:00 +0000' (or an equivalent instant as represented in some other
> timezone), or a timestamp of '2005-01-01 18:00:00' (as NZ is +1300 in
> January). I think this is the correct interpretation given what the
> javadoc says about setTimestamp() using the provided calendar to
> construct an appropriate SQL TIMESTAMP value. The JDBC spec may be
> lousy
> but I don't think they added the Calendar variants with the
> intention of
> having drivers completely ignore that parameter..
>
> I don't see how we can handle this even with TimeZone = UTC:
>
> If we pass '2005-01-01 05:00:00 +0000' as timestamptz then it is wrong
> if implicitly cast to timestamp; if we pass it as timestamp then it's
> just the wrong value immediately.
>
> If we pass '2005-01-01 18:00:00 +1300' as timestamptz then it still
> doesn't cast to the correct timestamp value; if we pass it as
> timestamp
> then it is the right timestamp value, but is wrong if it is then
> cast to
> timestamptz.
>
> Passing '2005-01-01 18:00:00 +1300' as an unknown type will work for
> both timestamp and timestamptz as there's no implicit casting
> involved,
> but we run the risk of getting the type wrong or not being able to
> determine a type at all. Conversely, this is effectively what the V2
> protocol path is doing anyway.
>
> If we can't change the casting behaviour, and TimeZone hackery doesn't
> work, and we want to support both timestamp and timestamptz via the
> standard interfaces, then it seems like passing the parameters as
> UNKNOWN is the only practical option :(
>
> -O
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>
>