Re: Patch for JDBC timestamp problems - Mailing list pgsql-patches

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