From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Barry Lind <barry@xythos.com>
Cc: pgsql-patches@postgresql.org
Cc: pgsql-interfaces@postgresql.org
Subject: Re: [PATCHES] Patch for JDBC timestamp problems
Date: Sat, 13 Jan 2001 00:18:13 -0500 (EST)
Message-ID: <200101130518.AAA15022@candle.pha.pa.us> (raw)
In-Reply-To: <3A5FBF08.AA1888A8@xythos.com>
Thanks. Applied.
---------------------------------------------------------------------------
inline patch 1: interfaces/jdbc/org/postgresql/jdbc1/PreparedStatement.java, interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java, interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java and 1 more (+117/-57 lines) download inline-1.patch*** 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: pgman@candle.pha.pa.us, barry@xythos.com, pgsql-patches@postgresql.org
Subject: Re: [PATCHES] Patch for JDBC timestamp problems
In-Reply-To: <200101130518.AAA15022@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox