Re: Timestamp Conversion Woes Redux

I looked for this in the trail, and couldn't find it.
So here it is verbatim

setTimestamp with a calendar

Sets parameter number parameterIndex to x. When the DBMS does not
store time zone information, the driver will use cal to construct a
JDBC Timestamp value which it then sends to the database. With a
Calendar object the driver can calculate the Timestamp object taking
into account a custom time zone. If no Calendar object is specified,
the driver uses the time zone of the Virtual Machine that is running
the application.


So my interpretation is.

If we are storing a timestamptz then ignore cal and use the server
time zone
If we are storing a timestamp then use cal to calculate the
timestamp; if cal is null then use the JVM's  time zone.

getTimestamp is a little less clear

When a getTimestamp method is called, the driver converts a JDBC
TIMESTAMP instance, which is generally a string, to a
java.sql.Timestamp object, which is a milliseconds value. In order to
calculate the milliseconds, the driver takes into account the time
zone, information a DBMS may or may not store. Because a Timestamp
object itself has no way to keep track of time zone information, the
driver relies on a java.util.Calendar object to get this information.
If no Calendar object
is supplied the 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 time zone information, the driver will simply use that
and ignore a Calendar object that may be passed to it. Therefore to
be portable, an application should supply a Calendar object to a
getTimestamp method thereby not relying on the DBMS to supply a time
zone but still being able to use the time zone supplied by a DBMS
when one is available.


I'm unclear whether "provides time zone information" means the server
time zone, or time zone returned in the data.

That aside:

If the server provides time zone info, use that to create the
timestamp, regardless of calendar supplied
if the server does not provide time zone info, use calendar supplied,
if that is null or not supplied then use JVM calendar.

Dave


On 21-Jul-05, at 5:09 PM, Kevin Grittner wrote:

> Could you post the URL so that I can find the reference without
> searching the whole trail?  (My apologies if I missed this in an
> earlier
> message -- there have been so many on this topic that I sometimes
> skimmed.)
>
> That would seem to conflict with information from the URL below, from
> which I will paste a quote.
>
> http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/mapping.html
>
> Methods added in the JDBC 2.0 core API make it possible for the driver
> to take a specified time zone into account when calculating a date,
> time, or timestamp. The time zone information is included in a
> java.util.Calendar object that is passed to new versions of the
> methods
> for getting and setting Date, Time, and Timestamp values. When no time
> zone is specified, the driver uses the time zone of the virtual
> machine
> running the application when it calculates a date, time, or timestamp.
>
>
>
>>>> Dave Cramer <pg@fastcrypt.com> 07/21/05 3:52 PM >>>
>>>>
>
> On 21-Jul-05, at 1:07 PM, Kevin Grittner wrote:
>
>
>> (1)  When storing a Timestamp object to a database column which is
>> defined as a timestamp with a time zone, what time zone should be
>> used?
>> Regardless of the choice, the value in the column must represent the
>> same moment as the original Timestamp object.  It seems clear that
>> some
>> methods allow you to specify a Calendar object for the sole
>> purpose of
>> specifying the time zone, and that in the absence of that, the
>> default
>> time zone of the JVM should be used.
>>
>>
> According to the JDBC API tutorial the time zone of the server
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>