Thread: Patch for JDBC timestamp problems
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*** 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
> There was another problem/fix mentioned on the email lists today by > mcannon@internet.com which is also fixed by this patch: Oh, great, I see this patch fixes Mike Cannon's problem too. I will apply it to the main tree now, and it will appear in 7.1. If you have any more improvements, please send them over. 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
Thanks. Applied.
---------------------------------------------------------------------------
*** 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
--
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
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
On Sat, 13 Jan 2001, Bruce Momjian wrote: > Thanks. Applied. > Can we start refraining from *reposting* the patch to the list? We saw it the first time, and the commit messages tell us it was applied ... *shrug*
> On Sat, 13 Jan 2001, Bruce Momjian wrote: > > > Thanks. Applied. > > > > Can we start refraining from *reposting* the patch to the list? We saw it > the first time, and the commit messages tell us it was applied ... *shrug* OK. -- 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
> Oh, great, I see this patch fixes Mike Cannon's problem too. I will
> apply it to the main tree now, and it will appear in 7.1. If you have
> any more improvements, please send them over. Thanks.
I'm confused, as usual. How does forcing the client time zone to GMT fix
the problem of mismatching client and server time zones? istm that you
still have a problem if the server is not on GMT.
Does creating a static variable affect the threadsafe capabilities of
the driver? If so, it shouldn't be done, but perhaps this is a feature
of the code already?
- Thomas
> > Oh, great, I see this patch fixes Mike Cannon's problem too. I will > > apply it to the main tree now, and it will appear in 7.1. If you have > > any more improvements, please send them over. Thanks. > > I'm confused, as usual. How does forcing the client time zone to GMT fix > the problem of mismatching client and server time zones? istm that you > still have a problem if the server is not on GMT. No idea. When it fixed someone's problem, I was excited. > > Does creating a static variable affect the threadsafe capabilities of > the driver? If so, it shouldn't be done, but perhaps this is a feature > of the code already? That was a question I had too. -- 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
> > I'm confused, as usual. How does forcing the client time zone to GMT fix
> > the problem of mismatching client and server time zones? istm that you
> > still have a problem if the server is not on GMT.
> No idea. When it fixed someone's problem, I was excited.
Well, afaict it might have broken most other people, so is not a big
win.
- Thomas
> > > I'm confused, as usual. How does forcing the client time zone to GMT fix > > > the problem of mismatching client and server time zones? istm that you > > > still have a problem if the server is not on GMT. > > No idea. When it fixed someone's problem, I was excited. > > Well, afaict it might have broken most other people, so is not a big > win. That bad, huh? The reason I posted the patch in the email is because I don't understand Java and wanted to make sure other people reviewed it, especially because we are in beta. It dealt with date/time stuff, which made it double-complex. I am ready to yank it if someone gives the word. -- 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
On Sat, 13 Jan 2001, Bruce Momjian wrote: > > > Oh, great, I see this patch fixes Mike Cannon's problem too. I will > > > apply it to the main tree now, and it will appear in 7.1. If you have > > > any more improvements, please send them over. Thanks. > > > > I'm confused, as usual. How does forcing the client time zone to GMT fix > > the problem of mismatching client and server time zones? istm that you > > still have a problem if the server is not on GMT. > > No idea. When it fixed someone's problem, I was excited. Since it obviously creates potential problems with others, please reverse said patch ASAP ...
On Sat, 13 Jan 2001, Bruce Momjian wrote: > > > > I'm confused, as usual. How does forcing the client time zone to GMT fix > > > > the problem of mismatching client and server time zones? istm that you > > > > still have a problem if the server is not on GMT. > > > No idea. When it fixed someone's problem, I was excited. > > > > Well, afaict it might have broken most other people, so is not a big > > win. > > That bad, huh? The reason I posted the patch in the email is because I > don't understand Java and wanted to make sure other people reviewed it, > especially because we are in beta. review is *before* you commit, not afterwards ... whether in beta period or not ... > It dealt with date/time stuff, which made it double-complex. I am ready > to yank it if someone gives the word. consider it given ... and please refrain from letting your "excitement" impair your judgement in future patch dealings ... review *before*, not after ...
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
Let me answer the questions Thomas raises: > I'm confused, as usual. How does forcing the client time zone to GMT fix > the problem of mismatching client and server time zones? istm that you > still have a problem if the server is not on GMT. Java has an internal representation for a date the is a long storing miliseconds since 1970, similar to unix. By using a date format object you convert this long value into strings that make sense to humans. Java also provides a SimpleDateFormat object that can format dates according to the standard Gregorian calendar, but java also allows other calendars/dateformat objects. Lets walk through a couple of examples: Consider a java Date object with the underlying long value of979419043128 Taking a SimpleDateFormat object and formatting this date to a String with the format mask of "yyyy-MM-dd hh:mm:ss.SSSzzz" will result in: 2001-01-13 20:50:43.128GMT if the timezone setting for the SimpleDateFormat object is GMT 2001-01-13 12:50:43.128PST if the timezone setting is PST ... So back to the original bug, the original code was simply performing a Timestamp.toString() which uses the following format mask to format the date 'yyyy-MM-dd hh:mm:ss.SSS' (notice no timezone is included in the format). So postgresql assumes the date value is in the DB server timezone and essentially appends on that timezone. So looking further at the example: DB Server running with EST (GMT-05), jdbc running with PST (GMT-08) given the date object above: Timestamp.toString() would format it as '2001-01-13 12:50:43.128' the server would receive this, see no timezone and assume the value was in EST and treat it as '2001-01-13 12:50:43.128-05' which is not what the client intended, and is the incorrect value, so when the client selects back the date and converts it to PST it will have '2001-01-13 09:50:43.128PST', three hours different than it sent to the server. So that was the problem, now to the solution. Java's SimpleDateFormat object formats timezone information as either the three letter code (i.e. PST) if the format mask has three or fewer z's in it, or as a full name (i.e. Pacific Standard Time) if the format mask has four or more z's in it. Whereas the ISO date format expected by postgres is of the format +/-XX (i.e. -08 for PST). So there were two ways to code a solution to this, a) figure out how to get from the java timezone object the offset from GMT and append on that value for timezone, or b) append on a known value (i.e. +00) for timezone, and set the timezone of the SimpleDateFormat object to timezone corresponding to it (i.e. GMT). As explained above setting the timezone on the SimpleDateFormat object doesn't change the absolute value of the date object, just how it gets formated. So going back to the above example: a) would have resulted in formatting the date as '2001-01-13 12:50:43.128-08' (if I could have figured out how to implement this easily) b) results in formatting the date as '2001-01-13 20:50:43.128+00' both a) and b) are simply different formats for the same underlying Date value of 979419043128, and both are interpreted by postgresql as the same timestamp value. > Does creating a static variable affect the threadsafe capabilities of > the driver? If so, it shouldn't be done, but perhaps this is a feature > of the code already? The Java core library objects are supposed to be thread safe unless documented otherwise, and since SimpleDateFormat is a core library object, it should be safe to use it from multiple threads simultaniously. However, I just looked at the source code for SimpleDateFormat and it is clearly *not* thread safe. So to work around this java bug I will resubmit the original patch to take this into account. thanks, --Barry Thomas Lockhart wrote: > > > Oh, great, I see this patch fixes Mike Cannon's problem too. I will > > apply it to the main tree now, and it will appear in 7.1. If you have > > any more improvements, please send them over. Thanks. > > I'm confused, as usual. How does forcing the client time zone to GMT fix > the problem of mismatching client and server time zones? istm that you > still have a problem if the server is not on GMT. > > Does creating a static variable affect the threadsafe capabilities of > the driver? If so, it shouldn't be done, but perhaps this is a feature > of the code already? > > - Thomas
Bruce, please pass *any* JDBC related patches by Peter Mount
(peter@retep.org.uk) , unless you've recently become a JDBC expert? AS
Thomas pointed out, not only did you miss the GMT implications, but you
also missed the threadsafe implications :(
On Sat, 13 Jan 2001, Barry Lind wrote:
> Let me answer the questions Thomas raises:
>
> > I'm confused, as usual. How does forcing the client time zone to GMT fix
> > the problem of mismatching client and server time zones? istm that you
> > still have a problem if the server is not on GMT.
>
> Java has an internal representation for a date the is a long storing
> miliseconds since 1970, similar to unix. By using a date format object
> you convert this long value into strings that make sense to humans.
> Java also provides a SimpleDateFormat object that can format dates
> according to the standard Gregorian calendar, but java also allows other
> calendars/dateformat objects. Lets walk through a couple of examples:
>
> Consider a java Date object with the underlying long value
> of979419043128
> Taking a SimpleDateFormat object and formatting this date to a String
> with the format mask of "yyyy-MM-dd hh:mm:ss.SSSzzz" will result in:
> 2001-01-13 20:50:43.128GMT if the timezone setting for the
> SimpleDateFormat object is GMT
> 2001-01-13 12:50:43.128PST if the timezone setting is PST
> ...
>
> So back to the original bug, the original code was simply performing a
> Timestamp.toString() which uses the following format mask to format the
> date 'yyyy-MM-dd hh:mm:ss.SSS' (notice no timezone is included in the
> format). So postgresql assumes the date value is in the DB server
> timezone and essentially appends on that timezone. So looking further
> at the example:
> DB Server running with EST (GMT-05), jdbc running with PST (GMT-08)
> given the date object above:
> Timestamp.toString() would format it as '2001-01-13 12:50:43.128' the
> server would receive this, see no timezone and assume the value was in
> EST and treat it as '2001-01-13 12:50:43.128-05' which is not what the
> client intended, and is the incorrect value, so when the client selects
> back the date and converts it to PST it will have '2001-01-13
> 09:50:43.128PST', three hours different than it sent to the server.
>
> So that was the problem, now to the solution. Java's SimpleDateFormat
> object formats timezone information as either the three letter code
> (i.e. PST) if the format mask has three or fewer z's in it, or as a full
> name (i.e. Pacific Standard Time) if the format mask has four or more
> z's in it. Whereas the ISO date format expected by postgres is of the
> format +/-XX (i.e. -08 for PST). So there were two ways to code a
> solution to this, a) figure out how to get from the java timezone object
> the offset from GMT and append on that value for timezone, or b) append
> on a known value (i.e. +00) for timezone, and set the timezone of the
> SimpleDateFormat object to timezone corresponding to it (i.e. GMT). As
> explained above setting the timezone on the SimpleDateFormat object
> doesn't change the absolute value of the date object, just how it gets
> formated. So going back to the above example:
>
> a) would have resulted in formatting the date as '2001-01-13
> 12:50:43.128-08' (if I could have figured out how to implement this
> easily)
> b) results in formatting the date as '2001-01-13 20:50:43.128+00'
> both a) and b) are simply different formats for the same underlying Date
> value of 979419043128, and both are interpreted by postgresql as the
> same timestamp value.
>
>
> > Does creating a static variable affect the threadsafe capabilities of
> > the driver? If so, it shouldn't be done, but perhaps this is a feature
> > of the code already?
>
> The Java core library objects are supposed to be thread safe unless
> documented otherwise, and since SimpleDateFormat is a core library
> object, it should be safe to use it from multiple threads
> simultaniously. However, I just looked at the source code for
> SimpleDateFormat and it is clearly *not* thread safe. So to work around
> this java bug I will resubmit the original patch to take this into
> account.
>
> thanks,
> --Barry
>
> Thomas Lockhart wrote:
> >
> > > Oh, great, I see this patch fixes Mike Cannon's problem too. I will
> > > apply it to the main tree now, and it will appear in 7.1. If you have
> > > any more improvements, please send them over. Thanks.
> >
> > I'm confused, as usual. How does forcing the client time zone to GMT fix
> > the problem of mismatching client and server time zones? istm that you
> > still have a problem if the server is not on GMT.
> >
> > Does creating a static variable affect the threadsafe capabilities of
> > the driver? If so, it shouldn't be done, but perhaps this is a feature
> > of the code already?
> >
> > - Thomas
>
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
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@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
I've been reading the recent patch messages for issues related to the JDBC driver, and I read this notice: > 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. In fact, the SimpleDateFormat class is not threadsafe, and you do need to instantiate a new SimpleDateFormat for each pass through the thread. It is more efficient to have a static instance; unfortunately, it is not correct. It is desirable to centralize the creation of each type of DateFormat, but the way to do it is a series of getMyDateFormat() calls that instantiate a new instance of the variety of SimpleDateFormat you want to get. See <http://developer.java.sun.com/developer/bugParade/bugs/4093418.html> for details (this requires registration with the Sun Developer Connection). -- Richard Bullington-McGuire <rbulling@microstate.com> Chief Technology Officer, The Microstate Corporation Phone: 703-796-6446 URL: http://www.microstate.com/ PGP key IDs: RSA: 0x93862305 DH/DSS: 0xDAC3028E
What was the conclusion of this discussion? Do we leave it static?
> 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@selectacast.net
> Increase signal to noise ratio. http://www.targabot.com
>
--
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
No, it cannot be static.
Bruce Momjian wrote:
>
> What was the conclusion of this discussion? Do we leave it static?
>
> > 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@selectacast.net
> > Increase signal to noise ratio. http://www.targabot.com
> >
>
> --
> 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
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com