Re: Patch for JDBC timestamp problems

Barry Lind wrote:
>
> Attached is a set of patches for a couple of bugs dealing with
> timestamps in JDBC.
>
> Bug#1) Incorrect timestamp stored in DB if client timezone different
> than DB.
>
> The buggy implementation of setTimestamp() in PreparedStatement simply
> used the toString() method of the java.sql.Timestamp object to convert
> to a string to send to the database. The format of this is yyyy-MM-dd
> hh:mm:ss.SSS which doesn't include any timezone information. Therefore
> the DB assumes its timezone since none is specified. That is OK if the
> timezone of the client and server are the same, however if they are
> different the wrong timestamp is received by the server. For example if
> the client is running in timezone GMT and wants to send the timestamp
> for noon to a server running in PST (GMT-8 hours), then the server will
> receive 2000-01-12 12:00:00.0 and interprete it as 2000-01-12
> 12:00:00-08 which is 2000-01-12 04:00:00 in GMT. The fix is to send a
> format to the server that includes the timezone offset. For simplicity
> sake the fix uses a SimpleDateFormat object with its timezone set to GMT
> so that '+00' can be used as the timezone for postgresql. This is done
> as SimpleDateFormat doesn't support formating timezones in the way
> postgresql expects.
>
> Bug#2) Incorrect handling of partial seconds in getting timestamps from
> the DB
>
> When the SimpleDateFormat object parses a string with a format like
> yyyy-MM-dd hh:mm:ss.SS it expects the fractional seconds to be three
> decimal places (time precision in java is miliseconds = three decimal
> places). This seems like a bug in java to me, but it is unlikely to be
> fixed anytime soon, so the postgresql code needed modification to
> support the java behaviour. So for example a string of '2000-01-12
> 12:00:00.12-08' coming from the database was being converted to a
> timestamp object with a value of 2000-01-12 12:00:00.012GMT-08:00. The
> fix was to check for a '.' in the string and if one is found append on
> an extra zero to the fractional seconds part.
>
> Bug#3) Performance problems
>
> In fixing the above two bugs, I noticed some things that could be
> improved. In PreparedStatement.setTimestamp(),
> PreparedStatement.setDate(), ResultSet.getTimestamp(), and
> ResultSet.getDate() these methods were creating a new SimpleDateFormat
> object everytime they were called. To avoid this unnecessary object
> creation overhead, I changed the code to use static variables for
> keeping a single instance of the needed formating objects.
> Also the code used the + operator for string concatenation. As everyone
> should know this is very inefficient and the use of StringBuffers is
> prefered.
>

While the java spec says that a+b+c should be converted into
a.concat(b.toString()).concat(c.toString())
probably every single java compiler (including javac) uses
StringBuffers. The only case where it is an advantage to use your own
stringBuffer is in a case like:

StringBuffer sb = new StringBuffer("blah");
sb.append(a+b+c);

Since that would create a temporary StringBuffer to calculate a+b+c just
to append to the original sb it it might be better to explictly append
a,b,and c.

Using static SimpleDateFormats will probably not cause threading
issues. Common sense says that if the set methods are never called on
them there will be no state change that my cause sync problems. But the
spec doesn't garuntee it. Personally I would have no problem using
static SimpleDateFormats if this were my code.

--
Joseph Shraibman
jks(at)selectacast(dot)net
Increase signal to noise ratio. http://www.targabot.com