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

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

The fact the two drivers return completely different results is a concern as they both should behave the same for this
datatype for consistency, but I can work around that via the string binding if the time zone is returned....but ODBC
cannever give me the time zone!  I think this is a serious bug.  I can work around this by assuming the same time zone
asthe 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
asa string. 

Regards,
Walter


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.

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


--
Adrian Klaver
adrian.klaver@aklaver.com

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.

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.

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
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
loseof 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
correctthis to be JDBC spec compliant caused some headaches for some people using the driver as they wrote code
assumingthis 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
givenit to me. There is no sane reason to have the time zone part stripped out before giving it to me.  At least with
JDBCit gave me an EQUALLY ACCURATE string of "2002-04-10 19:22:08.12345-04", with ODBC I need to run a separate query
toget 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
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. 


You still have not shown what it is you are actually doing. Personally,
less rant and more actual code would be useful. You originally asked for
a solution and I would like to see that, but at this point there is not
enough information to go on, for me at least.

So what exactly do you mean when you say 'asking for string form of
timestamp with time zone'?

>
> Regards,
> Walter


--
Adrian Klaver
adrian.klaver@aklaver.com

(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?

regards,
Hiroshi INoue


--
I am using the free version of SPAMfighter.
SPAMfighter has removed 10706 of my spam emails to date.
Get the free SPAMfighter here: http://www.spamfighter.com/len

Do you have a slow PC? Try a Free scan
http://www.spamfighter.com/SLOW-PCfighter?cid=sigen

(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?

Hi Walter,

I would enable time zone related code and complete the coding.
When I make a patch, could you test it?

(2014/06/10 19:31), Walter Couto wrote:
> Yes, I have to workaround this issue in my application since the
> existing issue as the current drivers have this bug. And to avoid
> overhead caching the server time zone is the best option to avoid
> repeated calls (thought their is a risk of you being wrong as you can
> changes the session time zone via a statement which will make the cached
> value wrong if it was run before that.
>
> As apposed to showing my code to get a SQL_C_CHAR  I'll show you the
> ODBC code that is perplexing:
>
> In convert.c's copy_and_convert_field() line 946: This case statement
> block states that for PG_TYPE_ABSTIME, PG_TYPE_DATETIME,
> PG_TYPE_TIMESTAMP_NO_TMZONE and PG_TYPE_TIMESTAMP that the string given
> by the server is:
>
> 1) -Infinity
>
> 2) -Infinity
>
> 3) invalid
>
> 4) %4d-%2d-%2d %2d:%2d:%2d%31s %15s (YYYY-MM-dd
> hh:mm:ss[.ffffff][-/+zh[:zm] [BC]) (max 35 characters for year < 10000)
>
> What that code does for 1) - 3) is perfectly fine, you have to choose
> some timestamp value and those values are as good as anything else. 4)
> is dealt by the function timestamp2stime on line 224 of the
> convert_field.  The only bug I see in this code is how it handles the
> time zone parsing, it only handles +/-zh, but not the possible but rare
> +/-zh:zm format/ That bug is in the switch statement on line 245 of that
> file. The cases for '+', '-' and '.' all don't check for the existence
> of ":" in the string.

Thanks.
I was surprized that the time zone in old Japanese days was
    +09:18:59
.
In fact using psql I see the following results.

=# select cast('1887-12-31 23:59:59' as timestamp with time zone);
          timestamptz
------------------------------
  1887-12-31 23:59:59+09:18:59

=# select cast('1887-12-31 23:59:60' as timestamp with time zone);
       timestamptz
------------------------
  1888-01-01 00:00:00+09

So I would take the form -/+zh[:zm[:zs]] into account.

regards,
Hiroshi Inoue

  ­­


--
I am using the free version of SPAMfighter.
SPAMfighter has removed 10739 of my spam emails to date.
Get the free SPAMfighter here: http://www.spamfighter.com/len

Do you have a slow PC? Try a Free scan
http://www.spamfighter.com/SLOW-PCfighter?cid=sigen

Hi Walter and all,

While reading the old and disabled code, I remembered the circumstances
back then a little.
Psqlodbc had followed the formatting rule e.g. described at the last
line in
  http://msdn.microsoft.com/en-us/library/ms712387%28v=vs.85%29.aspx
.
Also look at
  http://msdn.microsoft.com/en-us/library/ms712360%28v=vs.85%29.aspx
.
There's no time zone in ODBC world and I'm afraid the possibility
that some applications are broken by adding time zone expression.

So please vote which way to choose?

1. Keep the current behavior, i.e. simply drop the time zone part
    from server's output if exists.
2. Use local time zone instead of server time zone but omit the time
    zone expression.
3. Use local time zone with the time zone expression when the data
    type is TIMESTAMP WITH TIME ZONE.
4. The others.

Anyway I attached a patch which implements the above 3.
It's easy to change to 2.

regarts,
Hiroshi Inoue

> Walter
>
>
> -------- Original message --------
> From: "Inoue, Hiroshi"
> Date:06/10/2014 11:39 PM (GMT-05:00)
> To: Walter Couto ,David Johnston
> Cc: Adrian Klaver ,pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Re: Inconsistency between JDBC and ODBC drivers when
> dealing with TIMESTAMP WITH TIME ZONE
>
> Hi Walter,
>
> I would enable time zone related code and complete the coding.
> When I make a patch, could you test it?