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

(2014/06/10 12:18), Adrian Klaver wrote:

On 06/09/2014 07:49 PM, Walter Couto wrote:

I have to strongly disagree that this is an enhancement.  It is a
severe bug. In PostgreSQL, if you look at both the JDBC and 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 of time zone info.
I have NO issue with this (as a side note the JDBC and ODBC driver did
behave the same at one point, in both cases they returned the
timestamp in the server time zone, but surprise change in a recent
version to correct this to be JDBC spec compliant caused some
headaches for some people using the driver as they wrote code assuming
this behaviour and 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 it to me. There is no
sane reason to have the time zone part stripped out before giving it
to me.  At least with JDBC it gave me an EQUALLY ACCURATE string of
"2002-04-10 19:22:08.12345-04", with ODBC I need to run a separate
query to get the session 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 are given to our
application, we can't expect to ask the customers to work around
driver bugs and change all their perfectly working queries. I expect
the driver to give information as accurately as the server gave to the
driver.  That is what I am asking here and that is what is not happing
in the case of ODBC when asking for the string form of a timestamp
with time zone.  This is why I see this as a severe bug as the driver
is giving me CORRUPT data that is not the same or even equally the
same as the data it retrieved from the server that the client tool has
to "fix" by issuing another query.

Currently psqlodbc retrieves TIMESTAMP WITH TIME ZONE pretty much
the same as TIMESTAMP WITHOUT TIME ZONE. Though there are some code
 about time zone handling, it is disabled.

The data type TIMESTAMP_STRUCT which correponds to SQL_C_TIMESTAMP
has no information about time zone. If we would retrieve time zone
explictly, a consensus is needed which time zone is used to store
TIMESTAMP_STRUCT type data.

1. UTC
2. client local time zone
3. server time zone

The driver's behavior has been 3. So UTC seems unfavorable.
Probably most people have used the driver under environment
2 == 3. Should we use client local time zone instead?