Re: Re: Inconsistency between JDBC and ODBC drivers when dealing with TIMESTAMP WITH TIME ZONE - Mailing list pgsql-odbc

I have to strongly disagree that this is an enhancement.  It is a severe bug. In PostgreSQL, if you look at both the
JDBCand ODBC source code, for timestamp with time zone, the string that the server gives to the driver for my example
is"2002-04-11 02:33:08.12345+3"  if the session time zone is set to +3. 

Asking for a timestamp with time zone as a timestamp in either ODBC and JDBC is done knowing you are accepting the lose
oftime zone info. I have NO issue with this (as a side note the JDBC and ODBC driver did behave the same at one point,
inboth cases they returned the timestamp in the server time zone, but surprise change in a recent version to correct
thisto be JDBC spec compliant caused some headaches for some people using the driver as they wrote code assuming this
behaviourand now were all wrong). 

I have an issue with the retuned string value from ODBC....the driver got a perfectly fine string, it could have given
itto me. There is no sane reason to have the time zone part stripped out before giving it to me.  At least with JDBC it
gaveme an EQUALLY ACCURATE string of "2002-04-10 19:22:08.12345-04", with ODBC I need to run a separate query to get
thesession time zone to correct the string that was perfectly fine when ODBC was given it. 

Changing the query is nice for internal queries and is a good work around for this bug, but when non-internal queries
aregiven to our application, we can't expect to ask the customers to work around driver bugs and change all their
perfectlyworking queries. I expect the driver to give information as accurately as the server gave to the driver.  That
iswhat I am asking here and that is what is not happing in the case of ODBC when asking for the string form of a
timestampwith time zone.  This is why I see this as a severe bug as the driver is giving me CORRUPT data that is not
thesame or even equally the same as the data it retrieved from the server that the client tool has to "fix" by issuing
anotherquery. 

Regards,
Walter

________________________________________
From: pgsql-odbc-owner@postgresql.org [pgsql-odbc-owner@postgresql.org] on behalf of David G Johnston
[david.g.johnston@gmail.com]
Sent: Monday, June 09, 2014 8:18 PM
To: pgsql-odbc@postgresql.org
Subject: [ODBC] 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.


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

CONFIDENTIALITY NOTICE: This email message is for the sole use of the intended recipient(s) and may contain
confidentialand privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you
arenot the intended recipient, please contact the sender by reply email and destroy all copies of the original
message.