Re: 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?

(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