Re: [PATCHES] Patch for JDBC timestamp problems - Mailing list pgsql-interfaces
From | Bruce Momjian |
---|---|
Subject | Re: [PATCHES] Patch for JDBC timestamp problems |
Date | |
Msg-id | 200101131853.NAA20477@candle.pha.pa.us Whole thread Raw |
List | pgsql-interfaces |
I have backed out this patch because of concerns that have been raised. I would be glad to re-apply it after these issues have been addressed. > I have applied this patch. It fixes a number of bugs, even one that was > just reported a few day ago. Thanks. > > > --------------------------------------------------------------------------- > > > 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. > > I also did some cleanup in ResultSet.getTimestamp(). This method has > had multiple patches applied some of which resulted in code that was no > longer needed. For example the ISO timestamp format that postgresql > uses specifies the timezone as an offset like '-08'. Code was added at > one point to convert the postgresql format to the java one which is > GMT-08:00, however the old code was left around which did nothing. So > there was code that looked for yyyy-MM-dd hh:mm:sszzzzzzzzz and > yyyy-MM-dd hh:mm:sszzz. This second format would never be encountered > because zzz (i.e. -08) would be converted into the former (also note > that the SimpleDateFormat object treats zzzzzzzzz and zzz the same, the > number of z's does not matter). > > > There was another problem/fix mentioned on the email lists today by > mcannon@internet.com which is also fixed by this patch: > > Bug#4) Fractional seconds lost when getting timestamp from the DB > A patch by Jan Thomea handled the case of yyyy-MM-dd hh:mm:sszzzzzzzzz > but not the fractional seconds version yyyy-MM-dd hh:mm:ss.SSzzzzzzzzz. > The code is fixed to handle this case as well. > > thanks, > --Barry > --------------F902A91CC78EA5B0218A576D > Content-Type: text/plain; charset=UTF-8; name="patch.diff" > Content-Transfer-Encoding: 7bit > Content-Disposition: inline; filename="patch.diff" > > *** interfaces/jdbc/org/postgresql/jdbc1/PreparedStatement.java.orig Fri Jan 12 17:17:48 2001 > --- interfaces/jdbc/org/postgresql/jdbc1/PreparedStatement.java Fri Jan 12 17:27:28 2001 > *************** > *** 310,321 **** > * @param x the parameter value > * @exception SQLException if a database access error occurs > */ > public void setDate(int parameterIndex, java.sql.Date x) throws SQLException > { > ! SimpleDateFormat df = new SimpleDateFormat("''yyyy-MM-dd''"); > ! > ! set(parameterIndex, df.format(x)); > ! > // The above is how the date should be handled. > // > // However, in JDK's prior to 1.1.6 (confirmed with the > --- 310,320 ---- > * @param x the parameter value > * @exception SQLException if a database access error occurs > */ > + private static final SimpleDateFormat DF1 = new SimpleDateFormat("yyyy-MM-dd"); > public void setDate(int parameterIndex, java.sql.Date x) throws SQLException > { > ! set(parameterIndex, DF1.format(x)); > ! > // The above is how the date should be handled. > // > // However, in JDK's prior to 1.1.6 (confirmed with the > *************** > *** 349,357 **** > * @param x the parameter value > * @exception SQLException if a database access error occurs > */ > public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException > ! { > ! set(parameterIndex, "'" + x.toString() + "'"); > } > > /** > --- 348,364 ---- > * @param x the parameter value > * @exception SQLException if a database access error occurs > */ > + private static SimpleDateFormat DF2 = getDF2(); > + private static SimpleDateFormat getDF2() { > + SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); > + sdf.setTimeZone(TimeZone.getTimeZone("GMT")); > + return sdf; > + } > public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException > ! { > ! StringBuffer strBuf = new StringBuffer("'"); > ! strBuf.append(DF2.format(x)).append('.').append(x.getNanos()/10000000).append("+00'"); > ! set(parameterIndex, strBuf.toString()); > } > > /** > *** interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java.orig Fri Jan 12 17:18:45 2001 > --- interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java Fri Jan 12 17:25:02 2001 > *************** > *** 412,420 **** > String s = getString(columnIndex); > if(s==null) > return null; > - SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); > try { > ! return new java.sql.Date(df.parse(s).getTime()); > } catch (ParseException e) { > throw new PSQLException("postgresql.res.baddate",new Integer(e.getErrorOffset()),s); > } > --- 412,419 ---- > String s = getString(columnIndex); > if(s==null) > return null; > try { > ! return new java.sql.Date(DF5.parse(s).getTime()); > } catch (ParseException e) { > throw new PSQLException("postgresql.res.baddate",new Integer(e.getErrorOffset()),s); > } > *************** > *** 457,486 **** > * @return the column value; null if SQL NULL > * @exception SQLException if a database access error occurs > */ > public Timestamp getTimestamp(int columnIndex) throws SQLException > { > String s = getString(columnIndex); > if(s==null) > return null; > ! > ! // This works, but it's commented out because Michael Stephenson's > ! // solution is better still: > ! //SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); > ! > ! // Michael Stephenson's solution: > SimpleDateFormat df = null; > ! if (s.length()>21 && s.indexOf('.') != -1) { > ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzz"); > ! } else if (s.length()>19 && s.indexOf('.') == -1) { > ! df = new SimpleDateFormat("yyyy-MM-dd HH:MM:sszzz"); > ! } else if (s.length()>19 && s.indexOf('.') != -1) { > ! df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss.SS"); > ! } else if (s.length()>10 && s.length()<=18) { > ! df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss"); > } else { > ! df = new SimpleDateFormat("yyyy-MM-dd"); > } > ! > try { > return new Timestamp(df.parse(s).getTime()); > } catch(ParseException e) { > --- 456,514 ---- > * @return the column value; null if SQL NULL > * @exception SQLException if a database access error occurs > */ > + private static final SimpleDateFormat DF1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz"); > + private static final SimpleDateFormat DF2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz"); > + private static final SimpleDateFormat DF3 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); > + private static final SimpleDateFormat DF4 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); > + private static final SimpleDateFormat DF5 = new SimpleDateFormat("yyyy-MM-dd"); > public Timestamp getTimestamp(int columnIndex) throws SQLException > { > String s = getString(columnIndex); > if(s==null) > return null; > ! > ! boolean subsecond; > ! //if string contains a '.' we have fractional seconds > ! if (s.indexOf('.') == -1) { > ! subsecond = false; > ! } else { > ! subsecond = true; > ! } > ! > ! //here we are modifying the string from ISO format to a format java can understand > ! //java expects timezone info as 'GMT-08:00' instead of '-08' in postgres ISO format > ! //and java expects three digits if fractional seconds are present instead of two for postgres > ! //so this code strips off timezone info and adds on the GMT+/-... > ! //as well as adds a third digit for partial seconds if necessary > ! StringBuffer strBuf = new StringBuffer(s); > ! char sub = strBuf.charAt(strBuf.length()-3); > ! if (sub == '+' || sub == '-') { > ! strBuf.setLength(strBuf.length()-3); > ! if (subsecond) { > ! strBuf = strBuf.append('0').append("GMT").append(s.substring(s.length()-3, s.length())).append(":00"); > ! } else { > ! strBuf = strBuf.append("GMT").append(s.substring(s.length()-3, s.length())).append(":00"); > ! } > ! } else if (subsecond) { > ! strBuf = strBuf.append('0'); > ! } > ! > ! s = strBuf.toString(); > ! > SimpleDateFormat df = null; > ! > ! if (s.length()>23 && subsecond) { > ! df = DF1; > ! } else if (s.length()>23 && !subsecond) { > ! df = DF2; > ! } else if (s.length()>10 && subsecond) { > ! df = DF3; > ! } else if (s.length()>10 && !subsecond) { > ! df = DF4; > } else { > ! df = DF5; > } > ! > try { > return new Timestamp(df.parse(s).getTime()); > } catch(ParseException e) { > *** interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java.orig Fri Jan 12 17:40:55 2001 > --- interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java Fri Jan 12 17:47:42 2001 > *************** > *** 310,321 **** > * @param x the parameter value > * @exception SQLException if a database access error occurs > */ > public void setDate(int parameterIndex, java.sql.Date x) throws SQLException > { > ! SimpleDateFormat df = new SimpleDateFormat("''yyyy-MM-dd''"); > ! > ! set(parameterIndex, df.format(x)); > ! > // The above is how the date should be handled. > // > // However, in JDK's prior to 1.1.6 (confirmed with the > --- 310,320 ---- > * @param x the parameter value > * @exception SQLException if a database access error occurs > */ > + private static final SimpleDateFormat DF1 = new SimpleDateFormat("yyyy-MM-dd"); > public void setDate(int parameterIndex, java.sql.Date x) throws SQLException > { > ! set(parameterIndex, DF1.format(x)); > ! > // The above is how the date should be handled. > // > // However, in JDK's prior to 1.1.6 (confirmed with the > *************** > *** 349,357 **** > * @param x the parameter value > * @exception SQLException if a database access error occurs > */ > public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException > ! { > ! set(parameterIndex, "'" + x.toString() + "'"); > } > > /** > --- 348,364 ---- > * @param x the parameter value > * @exception SQLException if a database access error occurs > */ > + private static SimpleDateFormat DF2 = getDF2(); > + private static SimpleDateFormat getDF2() { > + SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); > + sdf.setTimeZone(TimeZone.getTimeZone("GMT")); > + return sdf; > + } > public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException > ! { > ! StringBuffer strBuf = new StringBuffer("'"); > ! strBuf.append(DF2.format(x)).append('.').append(x.getNanos()/10000000).append("+00'"); > ! set(parameterIndex, strBuf.toString()); > } > > /** > *** interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java.orig Fri Jan 12 16:47:28 2001 > --- interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java Fri Jan 12 17:03:03 2001 > *************** > *** 415,423 **** > String s = getString(columnIndex); > if(s==null) > return null; > - SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd"); > try { > ! return new java.sql.Date(df.parse(s).getTime()); > } catch (ParseException e) { > throw new PSQLException("postgresql.res.baddate",new Integer(e.getErrorOffset()),s); > } > --- 415,422 ---- > String s = getString(columnIndex); > if(s==null) > return null; > try { > ! return new java.sql.Date(DF5.parse(s).getTime()); > } catch (ParseException e) { > throw new PSQLException("postgresql.res.baddate",new Integer(e.getErrorOffset()),s); > } > *************** > *** 460,506 **** > * @return the column value; null if SQL NULL > * @exception SQLException if a database access error occurs > */ > public Timestamp getTimestamp(int columnIndex) throws SQLException > { > String s = getString(columnIndex); > if(s==null) > return null; > ! > ! // This works, but it's commented out because Michael Stephenson's > ! // solution is better still: > ! //SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); > ! // Modification by Jan Thomae > ! String sub = s.substring(s.length() - 3, s.length()-2); > ! if (sub.equals("+") || sub.equals("-")) { > ! s = s.substring(0, s.length()-3) + "GMT"+ s.substring(s.length()-3, s.length())+":00"; > } > ! // ------- > ! // Michael Stephenson's solution: > SimpleDateFormat df = null; > > ! // Modification by Jan Thomae > ! if (s.length()>27) { > ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz"); > ! } else > ! // ------- > ! if (s.length()>21 && s.indexOf('.') != -1) { > ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzz"); > ! } else if (s.length()>19 && s.indexOf('.') == -1) { > ! df = new SimpleDateFormat("yyyy-MM-dd HH:MM:sszzz"); > ! } else if (s.length()>19 && s.indexOf('.') != -1) { > ! df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss.SS"); > ! } else if (s.length()>10 && s.length()<=18) { > ! df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss"); > } else { > ! df = new SimpleDateFormat("yyyy-MM-dd"); > } > ! > try { > return new Timestamp(df.parse(s).getTime()); > } catch(ParseException e) { > throw new PSQLException("postgresql.res.badtimestamp",new Integer(e.getErrorOffset()),s); > } > } > > /** > * A column value can be retrieved as a stream of ASCII characters > --- 459,524 ---- > * @return the column value; null if SQL NULL > * @exception SQLException if a database access error occurs > */ > + private static final SimpleDateFormat DF1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz"); > + private static final SimpleDateFormat DF2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz"); > + private static final SimpleDateFormat DF3 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); > + private static final SimpleDateFormat DF4 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); > + private static final SimpleDateFormat DF5 = new SimpleDateFormat("yyyy-MM-dd"); > public Timestamp getTimestamp(int columnIndex) throws SQLException > { > String s = getString(columnIndex); > if(s==null) > return null; > ! > ! boolean subsecond; > ! //if string contains a '.' we have fractional seconds > ! if (s.indexOf('.') == -1) { > ! subsecond = false; > ! } else { > ! subsecond = true; > ! } > ! > ! //here we are modifying the string from ISO format to a format java can understand > ! //java expects timezone info as 'GMT-08:00' instead of '-08' in postgres ISO format > ! //and java expects three digits if fractional seconds are present instead of two for postgres > ! //so this code strips off timezone info and adds on the GMT+/-... > ! //as well as adds a third digit for partial seconds if necessary > ! StringBuffer strBuf = new StringBuffer(s); > ! char sub = strBuf.charAt(strBuf.length()-3); > ! if (sub == '+' || sub == '-') { > ! strBuf.setLength(strBuf.length()-3); > ! if (subsecond) { > ! strBuf = strBuf.append('0').append("GMT").append(s.substring(s.length()-3, s.length())).append(":00"); > ! } else { > ! strBuf = strBuf.append("GMT").append(s.substring(s.length()-3, s.length())).append(":00"); > ! } > ! } else if (subsecond) { > ! strBuf = strBuf.append('0'); > } > ! > ! s = strBuf.toString(); > ! > SimpleDateFormat df = null; > > ! if (s.length()>23 && subsecond) { > ! df = DF1; > ! } else if (s.length()>23 && !subsecond) { > ! df = DF2; > ! } else if (s.length()>10 && subsecond) { > ! df = DF3; > ! } else if (s.length()>10 && !subsecond) { > ! df = DF4; > } else { > ! df = DF5; > } > ! > try { > return new Timestamp(df.parse(s).getTime()); > } catch(ParseException e) { > throw new PSQLException("postgresql.res.badtimestamp",new Integer(e.getErrorOffset()),s); > } > } > + > > /** > * A column value can be retrieved as a stream of ASCII characters > > --------------F902A91CC78EA5B0218A576D-- > > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
pgsql-interfaces by date: