Re: Timestamp Conversion Woes Redux

Thinking some more....

It would appear that the setTimestamp is intended to do one or the
other, but not both.

either you have timezone information or you don't. I think this
effectively revokes your license to
interpret the spec as you suggested below.

Further the JDBCCTS doesn't take have any facility for both
scenarios, you can choose the timestamp
type that you want it to pass with (assuming you have a choice).

Dave
On 20-Jul-05, at 5:55 AM, Dave Cramer wrote:

>
> On 19-Jul-05, at 11:00 PM, Oliver Jowett wrote:
>
>
>> Dave Cramer wrote:
>>
>>
>>
>>> Well, we're in a vague area of the spec here. There are two
>>> TIMESTAMP
>>> types defined by the sql spec,
>>>
>>>
>>
>> right.
>>
>>
>>
>>> and only one setTimestamp.
>>>
>>>
>>
>> The SQL spec doesn't define setTimestamp at all. The JDBC spec
>> defines
>> *two* setTimestamp methods.
>>
> This was implied...
>
>>
>>
>>
>>> There is no indication by the spec
>>> that this behaviour is the "right" behaviour.
>>>
>>>
>>
>> The javadoc for the with-Calendar variant is quite clear:
>>
>>
>>
>>>> Sets the designated parameter to the given java.sql.Timestamp
>>>> value,
>>>> using the given Calendar object. The driver uses the Calendar
>>>> object to
>>>> construct an SQL TIMESTAMP value, which the driver then sends to
>>>> the
>>>> database. With a Calendar object, the driver can calculate the
>>>> timestamp
>>>> taking into account a custom timezone. If no Calendar object is
>>>> specified, the driver uses the default timezone, which is that
>>>> of the
>>>> virtual machine running the application.
>>>>
>>>>
>>
>> I think it's pretty clear that this variant must map to timestamptz.
>>
>
> Well to add more confusion here is the excerpt from Sun's JDBC API
> Tutorial
>
> pg 1024 ( slightly condensed )
>
>
> The number of milliseconds in a Timestamp object always takes into
> account a time zone ...
>
> In order to calculate the milliseconds the driver takes into
> account the time zone, information the DBMS may or may not store.
> If no Calendar object is supplied the driver will use the default
> Calendar whose time zone is that of the JVM that is
> running the application. If the DBMS does provide timezone
> information the driver will simply use that and IGNORE (my caps) a
> Calendar object that may have been passed to it.
>
>
>
>
>>
>> The javadoc for the no-Calendar variant is more vague:
>>
>>
>>
>>>> Sets the designated parameter to the given java.sql.Timestamp
>>>> value. The
>>>> driver converts this to an SQL TIMESTAMP value when it sends it
>>>> to the
>>>> database.
>>>>
>>>>
>>
>> I'm willing to take that as license to map it to timestamp as
>> there's no
>> mention of timezone at all, no way to supply one, and "SQL TIMESTAMP"
>> defaults to WITHOUT TIME ZONE I believe.
>>
>> If you want to set a TIMESTAMP WITH TIMEZONE using the default
>> timezone,
>> the JDBC spec seems to want you to call setTimestamp(i, timestamp,
>> null)
>> (see the first javadoc excerpt above -- "if no calendar object is
>> specified"). The current driver also does this for
>> setTimestamp(i,timestamp), which is what I'd like to change.
>>
>> (and also fix setTimestamp(i,timestamp,calendar) to actually preserve
>> the timezone info you give it rather than mashing it into the default
>> timezone, but that's a separate issue..)
>>
>> -O
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>>
>>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

Dave Cramer
davec(at)postgresintl(dot)com
www.postgresintl.com
ICQ #14675561
jabber davecramer(at)jabber(dot)org
ph (519 939 0336 )