Re: Inconsistency between JDBC and ODBC drivers when dealing with TIMESTAMP WITH TIME ZONE

Walter Couto wrote
> The fact the two drivers return completely different results is a concern
> as they both should behave the same for this data type for consistency,

At face value this is not something that can be expected. JDBC and ODBC have
no requirement to be consistent between each other.

Your choice to omit displaying the timezone in the timestamp retrieval
(JDBC:getDate) is in error.  If both outputs resolve to the same
point-in-time there is no inconsistency to worry about.

JDBC is at least kind enough, when you ask to to provide a string
representation (JDBC:getString) of the timestamp, to show the timezone
information.  The fact that the ODBC driver does not is an enhancement worth
considering.

Also, the reason for some confusion is that you do not "bind" when you
retrieve a value from a result set.  Given that your insert was done
on-server and I'm doubting you are paramertizing the SELECT statement, you
never actually "bind" anything as its commonly used - with placeholders in
queries sent to the server.

I am not familiar with ODBC but given your first example I would have to
conclude you are getting sufficient and correct data from the server as long
as you leave it in a Date object and use your language methods to ensure
that upon display you are converting to the correct timezone.

Note that you can let PostgreSQL do the work by using:

SELECT timestampoffset_col AT TIME ZONE 'GMT' --which then returns a plain
timestamp adjusted to the specified timezone.  You can further ensure the
return value by formatting the above expression as text.

SELECT to_char(now() AT TIME ZONE 'GMT','YYYY-MM-DD HH24:MI:SS GMT');

David J.







--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Inconsistency-between-JDBC-and-ODBC-drivers-when-dealing-with-TIMESTAMP-WITH-TIME-ZONE-tp5806577p5806595.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.