pg.ddx.io pgsql-interfaces@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Revised Patch for JDBC timestamp problems 3+ messages / 2 participants [nested] [flat]
* Revised Patch for JDBC timestamp problems @ 2001-01-13 23:49 Barry Lind <barry@xythos.com> 2001-01-14 03:08 ` Re: [PATCHES] Revised Patch for JDBC timestamp problems Bruce Momjian <pgman@candle.pha.pa.us> 2001-01-24 23:41 ` Re: [PATCHES] Revised Patch for JDBC timestamp problems Bruce Momjian <pgman@candle.pha.pa.us> 0 siblings, 2 replies; 3+ messages in thread From: Barry Lind @ 2001-01-13 23:49 UTC (permalink / raw) To: pgsql-patches@postgresql.org; +Cc: pgsql-interfaces Attached is a revised patch that removes the static SimpleDateFormat objects that Thomas pointed out might be a problem. thanks, --Barry PS. Is this the correct way to submit patches by sending to pgsql-patches? It appeared from some of the comments on my last submission that there is some other approval/review process that I should go through first, but I don't see that documented anywhere, so if I am doing something wrong, please forgive me, and point me in the right direction. PPS. I have included and updated the comments from the original patch request to reflect the changes made in this revised patch. > 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. > > > 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#3) 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. Attachments:^ permalink raw reply [nested|flat] 3+ messages in threadpatch.diff (text/plain, 10.1K) download
*** interfaces/jdbc/org/postgresql/jdbc1/PreparedStatement.java.orig Fri Jan 12 17:17:48 2001 --- interfaces/jdbc/org/postgresql/jdbc1/PreparedStatement.java Sat Jan 13 15:32:09 2001 *************** *** 312,321 **** */ 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 --- 312,320 ---- */ 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 *************** *** 350,357 **** * @exception SQLException if a database access error occurs */ public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException ! { ! set(parameterIndex, "'" + x.toString() + "'"); } /** --- 349,360 ---- * @exception SQLException if a database access error occurs */ public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException ! { ! SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); ! df.setTimeZone(TimeZone.getTimeZone("GMT")); ! StringBuffer strBuf = new StringBuffer("'"); ! strBuf.append(df.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 Sat Jan 13 15:25:37 2001 *************** *** 462,486 **** 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) { --- 462,510 ---- 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 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz"); ! } else if (s.length()>23 && !subsecond) { ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz"); ! } else if (s.length()>10 && subsecond) { ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); ! } else if (s.length()>10 && !subsecond) { ! 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) { *** interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java.orig Fri Jan 12 17:40:55 2001 --- interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java Sat Jan 13 15:31:45 2001 *************** *** 312,321 **** */ 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 --- 312,321 ---- */ 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 *************** *** 350,357 **** * @exception SQLException if a database access error occurs */ public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException ! { ! set(parameterIndex, "'" + x.toString() + "'"); } /** --- 350,361 ---- * @exception SQLException if a database access error occurs */ public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException ! { ! SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); ! df.setTimeZone(TimeZone.getTimeZone("GMT")); ! StringBuffer strBuf = new StringBuffer("'"); ! strBuf.append(df.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 Sat Jan 13 15:16:14 2001 *************** *** 465,506 **** 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 --- 465,520 ---- 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 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz"); ! } else if (s.length()>23 && !subsecond) { ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz"); ! } else if (s.length()>10 && subsecond) { ! df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); ! } else if (s.length()>10 && !subsecond) { ! 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
* Re: [PATCHES] Revised Patch for JDBC timestamp problems 2001-01-13 23:49 Revised Patch for JDBC timestamp problems Barry Lind <barry@xythos.com> @ 2001-01-14 03:08 ` Bruce Momjian <pgman@candle.pha.pa.us> 1 sibling, 0 replies; 3+ messages in thread From: Bruce Momjian @ 2001-01-14 03:08 UTC (permalink / raw) To: Barry Lind <barry@xythos.com>; +Cc: pgsql-patches@postgresql.org; pgsql-interfaces > Attached is a revised patch that removes the static SimpleDateFormat > objects that Thomas pointed out might be a problem. Thanks. I will apply it as soon as I can. > PS. Is this the correct way to submit patches by sending to > pgsql-patches? It appeared from some of the comments on my last > submission that there is some other approval/review process that I > should go through first, but I don't see that documented anywhere, so if > I am doing something wrong, please forgive me, and point me in the right > direction. No, Barry, you are doing great. Seems we have an internal issue here because I don't understand the java code, so I have trouble evaluating it, and sometimes I am over-eager to get these jdbc fixes into the code. Please, just keep doing what you are doing. The JDBC driver needs all the help it can get. > PPS. I have included and updated the comments from the original patch > request to reflect the changes made in this revised patch. Thanks. -- 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [PATCHES] Revised Patch for JDBC timestamp problems 2001-01-13 23:49 Revised Patch for JDBC timestamp problems Barry Lind <barry@xythos.com> @ 2001-01-24 23:41 ` Bruce Momjian <pgman@candle.pha.pa.us> 1 sibling, 0 replies; 3+ messages in thread From: Bruce Momjian @ 2001-01-24 23:41 UTC (permalink / raw) To: Barry Lind <barry@xythos.com>; +Cc: pgsql-patches@postgresql.org, PostgreSQL jdbc list <pgsql-jdbc@postgresql.org> Patch applied after confirmation from Java users. Thanks. -- 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 Attachments: /bjm/0 (text/plain, 0B) download ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-01-24 23:41 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2001-01-13 23:49 Revised Patch for JDBC timestamp problems Barry Lind <barry@xythos.com> 2001-01-14 03:08 ` Bruce Momjian <pgman@candle.pha.pa.us> 2001-01-24 23:41 ` Bruce Momjian <pgman@candle.pha.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox