Re: Timestamp Conversion Woes Redux

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
>
>