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

On 06/09/2014 12:15 PM, Walter Couto wrote:
> CREATE TABLE DATE_TEST
> (
> test integer NOT NULL,
> TIMESTAMPOFFSET_COL TIMESTAMP WITH TIME ZONE,
> PRIMARY KEY(TEST)
> )
> ;
> INSERT INTO DATE_TEST VALUES ( 2, timestamp WITH TIME ZONE '2002-04-11 01:33:08.12345+02' );
> ;
>
> NOTE: My client machines time zone offset is currently -4. The server's times zone offset is +3.
>
> Execute:
>
> select TIMESTAMPOFFSET_COL from DATE_TEST where test = 2.
>
> Results:
> If you bind to that column as a timestamp, I get the following:

>
> JDBC: 2002-04-11 19:33:08.12345
> ODBC: 2002-04-11 02:33:08.12345
>
> If I bind as a string I get:
>
> JDBC: 2002-04-10 19:33:08.12345-04
> ODBC: 2002-04-11 02:33:08.12345

I am not following the above.

Are you talking about binding when you do the INSERT?

If so can you show the code for each case?

How and where are you doing the selects?

>
> 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, but I can work around that via the string binding if the time zone is returned....but ODBC can never give me the time zone! I think this is a serious bug. I can work around this by assuming the same time zone as the server, but I would then have to keep checking what that time zone offset is just in case the session time zone changed, but I think this is a waste of a query round trip since that info should have been available to me via binding as a string.

Looks to me like the JDBC driver is trying to convert to local time.

Just to be clear what JDBC driver are you using?
>
> Regards,
> Walter
>
>
> CONFIDENTIALITY NOTICE: This email message is for the sole use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com