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

On Tuesday, June 10, 2014, Inoue, Hiroshi <inoue(at)tpf(dot)co(dot)jp> wrote:

> (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?
>
>
There is no functional difference between 2 and 3 if the information sent
from the server cannot be embedded into the stored typed.

That is the issue since what the driver is providing when a string is
requested is not the raw server data but the string representation of the
server content after it has been converted into a local timestamp object
and the timezone stripped.

If indeed the timezone is silently dropped then it matters not what you
call resolving the issue - enhancement or bugfix. In the end someone has to
volunteer to dive into the open souce code and develop a patch. Until then
you live with the limitation, write a wrapper to accomodate a local
workaround (say by caching the server timezone to avoid repetitive
queries), and/or choose a different driver/protocol.

Note I doubt any acceptable solution would be simply returning supposedly
raw text from the server. I think one problem is that the data is not
always in text format. It is possible that an internal type could be
created but if you go that far you might as well go make the whole thing
work normally and not just via a getString method.

David J.