Re: [PATCHES] Patch for JDBC timestamp problems

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(at)internet(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026