Re: (2) patch against cvs for getTimestamp() problem.

Thomas,

I have reviewed this patch in detail (I thought I found a problem with
it, but I was wrong :-) ).  The only change I made was to your comments
in the code.  There is a reason that the YYYY-MM-DD format is supported
(according to the jdbc spec you can call getTimestamp() on a date
column).  I have tested both the jdbc1 and jdbc2 builds on my code base
and it works for me.

Given how close we are to the 7.2 release, I would appreciate it if
others tested any timestamp code they might have.  I have applied the
patch and built new jar files and placed them on the website.

thanks,
--Barry



Thomas O'Dowd wrote:

> As a followup to this I noticed a small typo in the jdbc1 part of the
> patch which is fixed in this patch. So please ignore the first patch
> that I sent. I'd like to add that the jdbc2 part is tested as far as
> I can but someone should briefly test the jdbc1 part as I haven't
> the environment to test that here.
>
> Regards,
>
> Tom.
>
> On Sat, Dec 08, 2001 at 02:40:51PM +0900, Thomas O'Dowd wrote:
>
>>Hi all,
>>
>>I just upgraded my development environment to 7.2b3 and the latest cvs
>>driver both built from source. I found that ResultSet.getTimestamp()
>>was no longer working against a 7.2 database and causing an exception
>>parsing the timestamp that pg was returning.
>>
>>Bad Timestamp Format at 23 in 2001-12-06 23:24:07.895882+09
>>    at org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1707)
>>    at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398)
>>
>>It seems that timestamps have become more accurate in 7.2 going from
>>2 fractional second digits to 6 which was causing the exception. Looking
>>at the code in toTimestamp() I decided that it was better to rewrite
>>it, then add a small fix as the method of parsing before was error
>>prone in my opinion and the new code (less the comments) is smaller
>>and easier to read IMHO :)
>>
>>I'm including patches against jdbc1 and jdbc2 ResultSet.java. I generated
>>the patch against the latest cvs, using cvs diff -c. I've tested the
>>jdbc2 versions pretty well and it parses the following combinations
>>
>>"2001-12-07 16:29:22.47+09",
>>"2001-12-07 16:29:26+09",
>>"2001-12-07 16:26:50.144213+09",
>>"2001-12-07 16:26:59.461349+09",
>>"2001-12-07 16:26:59.461349+05:30",
>>"2001-12-07 00:00:00+09",
>>"2001-12-07 12:00:00",
>>"2001-12-07"
>>
>>The driver is set to return ISO datestyle formats so I'm not sure if the
>>latter two formats are required but as the older code supported them, I've
>>added code to do this. If anyone can suggest more formats that pg may
>>return I can test those on the new code too. I've been running the new
>>driver with my code with no problem now.
>>
>>If anyone has any questions or suggestions please let me know. I think
>>this is an important bug fix for the 7.2 release of pg. Can someone test
>>the changes in the jdbc1 driver as I don't have a means of doing this.
>>
>>The patch file is attached.
>>
>>Regards,
>>
>>Tom.
>>--
>>Thomas O'Dowd. - Nooping - http://nooper.com
>>tom@nooper.com - Testing - http://nooper.co.jp/labs
>>
>>
>>------------------------------------------------------------------------
>>
>>Index: src/interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java
>>===================================================================
>>RCS file: /projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java,v
>>retrieving revision 1.27
>>diff -c -r1.27 ResultSet.java
>>*** src/interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java    2001/11/25 23:26:59    1.27
>>--- src/interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java    2001/12/08 05:18:03
>>***************
>>*** 492,497 ****
>>--- 492,505 ----
>>       * Get the value of a column in the current row as a
>>       * java.sql.Timestamp object
>>       *
>>+      * The driver is set to return ISO date formated strings. We modify this
>>+     * string from the ISO format to a format that Java can understand. Java
>>+     * expects timezone info as 'GMT+09:00' where as ISO gives '+09'.
>>+     * Java also expects fractional seconds to 3 places where postgres
>>+     * will give, none, 2 or 6 depending on the time and postgres version.
>>+     * From version 7.2 postgres returns fractional seconds to 6 places.
>>+     * If available, we drop the last 3 digits.
>>+      *
>>       * @param columnIndex the first column is 1, the second is 2...
>>       * @return the column value; null if SQL NULL
>>       * @exception SQLException if a database access error occurs
>>***************
>>*** 499,600 ****
>>      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);
>>!
>>!         //we are looking to see if the backend has appended on a timezone.
>>!         //currently postgresql will return +/-HH:MM or +/-HH for timezone offset
>>!         //(i.e. -06, or +06:30, note the expectation of the leading zero for the
>>!         //hours, and the use of the : for delimiter between hours and minutes)
>>!         //if the backend ISO format changes in the future this code will
>>!         //need to be changed as well
>>!         char sub = strBuf.charAt(strBuf.length() - 3);
>>!         if (sub == '+' || sub == '-')
>>!         {
>>!             strBuf.setLength(strBuf.length() - 3);
>>!             if (subsecond)
>>!             {
>>!                 strBuf.append('0').append("GMT").append(s.substring(s.length() - 3, s.length())).append(":00");
>>!             }
>>!             else
>>!             {
>>!                 strBuf.append("GMT").append(s.substring(s.length() - 3, s.length())).append(":00");
>>!             }
>>!         }
>>!         else if (sub == ':')
>>          {
>>!             //we may have found timezone info of format +/-HH:MM, or there is no
>>!             //timezone info at all and this is the : preceding the seconds
>>!             char sub2 = strBuf.charAt(strBuf.length() - 5);
>>!             if (sub2 == '+' || sub2 == '-')
>>              {
>>!                 //we have found timezone info of format +/-HH:MM
>>!                 strBuf.setLength(strBuf.length() - 5);
>>!                 if (subsecond)
>>!                 {
>>!                     strBuf.append('0').append("GMT").append(s.substring(s.length() - 5));
>>!                 }
>>!                 else
>>                  {
>>!                     strBuf.append("GMT").append(s.substring(s.length() - 5));
>>!                 }
>>              }
>>!             else if (subsecond)
>>              {
>>!                 strBuf.append('0');
>>              }
>>-         }
>>-         else if (subsecond)
>>-         {
>>-             strBuf = strBuf.append('0');
>>-         }
>>
>>!         s = strBuf.toString();
>>!
>>!         SimpleDateFormat df = null;
>>!
>>!         if (s.length() > 23 && subsecond)
>>!         {
>>!             df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz");
>>!         }
>>!         else if (s.length() > 23 && !subsecond)
>>!         {
>>!             df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz");
>>!         }
>>!         else if (s.length() > 10 && subsecond)
>>!         {
>>!             df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
>>!         }
>>!         else if (s.length() > 10 && !subsecond)
>>!         {
>>              df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
>>          }
>>          else
>>          {
>>              df = new SimpleDateFormat("yyyy-MM-dd");
>>          }
>>
>>          try
>>          {
>>!             return new Timestamp(df.parse(s).getTime());
>>          }
>>          catch (ParseException e)
>>          {
>>--- 507,593 ----
>>      public Timestamp getTimestamp(int columnIndex) throws SQLException
>>      {
>>          String s = getString(columnIndex);
>>+
>>          if (s == null)
>>              return null;
>>
>>!         StringBuffer sbuf = new StringBuffer(s);
>>!         SimpleDateFormat df = null;
>>
>>!         if (s.length() > 19)
>>          {
>>!             // The len of the ISO string to the second value is 19 chars. If
>>!             // greater then 19, there should be tz info and perhaps fractional
>>!             // second info which we need to change to java to read it.
>>!
>>!             // cut the copy to second value "2001-12-07 16:29:22"
>>!             int i = 19;
>>!             sbuf.setLength(i);
>>!
>>!             char c = s.charAt(i++);
>>!             if (c == '.')
>>              {
>>!                 // Found a fractional value. Append up to 3 digits including
>>!                 // the leading '.'
>>!                 do
>>                  {
>>!                     if (i < 24)
>>!                         sbuf.append(c);
>>!                     c = s.charAt(i++);
>>!                 } while (Character.isDigit(c));
>>!
>>!                 // If there wasn't at least 3 digits we should add some zeros
>>!                 // to make up the 3 digits we tell java to expect.
>>!                 for (int j = i; j < 24; j++)
>>!                     sbuf.append('0');
>>              }
>>!             else
>>              {
>>!                 // No fractional seconds, lets add some.
>>!                 sbuf.append(".000");
>>              }
>>
>>!             // prepend the GMT part and then add the remaining bit of
>>!             // the string.
>>!             sbuf.append(" GMT");
>>!             sbuf.append(c);
>>!             sbuf.append(s.substring(i, s.length()));
>>!
>>!             // Lastly, if the tz part doesn't specify the :MM part then
>>!             // we add ":00" for java.
>>!             if (s.length() - i < 5)
>>!                 sbuf.append(":00");
>>!
>>!             // we'll use this dateformat string to parse the result.
>>!             df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS z");
>>!         }
>>!         else if (s.length() == 19)
>>!         {
>>!             // No tz or fractional second info. We could add some, but
>>!             // then we have to figure out what tz we're in so its easier
>>!             // to just let Java do that for us. I'm not sure if this is
>>!             // possible to have to parse a string in this format, as pg
>>!             // should give us tz qualified timestamps back, but it was
>>!             // in the old code, so I'm handling it for now.
>>              df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
>>          }
>>          else
>>          {
>>+             // We must just a date. Again, I wonder if we have to handle
>>+             // this case, but it was in the old code and I've no time
>>+             // to figure it out.
>>              df = new SimpleDateFormat("yyyy-MM-dd");
>>          }
>>
>>+         // NOTE: if we don't have to handle the last two cases, then
>>+         // we can just initialise df to the first string in a resultset
>>+         // and use that throughout without having to create an object
>>+         // everytime. Leave this for now.
>>+
>>          try
>>          {
>>!             // All that's left is to parse the string and return the ts.
>>!             return new Timestamp(df.parse(sbuf.toString()).getTime());
>>          }
>>          catch (ParseException e)
>>          {
>>Index: src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java
>>===================================================================
>>RCS file: /projects/cvsroot/pgsql/src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java,v
>>retrieving revision 1.45
>>diff -c -r1.45 ResultSet.java
>>*** src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java    2001/11/25 23:26:59    1.45
>>--- src/interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java    2001/12/08 05:18:04
>>***************
>>*** 1591,1705 ****
>>          }
>>      }
>>
>>!     public static Timestamp toTimestamp(String s, ResultSet resultSet) throws SQLException
>>      {
>>          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
>>          synchronized (resultSet)
>>          {
>>!             // We must be synchronized here incase more theads access the ResultSet
>>!             // bad practice but possible. Anyhow this is to protect sbuf and
>>!             // SimpleDateFormat objects
>>
>>!             // First time?
>>              if (resultSet.sbuf == null)
>>                  resultSet.sbuf = new StringBuffer();
>>
>>!             resultSet.sbuf.setLength(0);
>>              resultSet.sbuf.append(s);
>>
>>!             //we are looking to see if the backend has appended on a timezone.
>>!             //currently postgresql will return +/-HH:MM or +/-HH for timezone offset
>>!             //(i.e. -06, or +06:30, note the expectation of the leading zero for the
>>!             //hours, and the use of the : for delimiter between hours and minutes)
>>!             //if the backend ISO format changes in the future this code will
>>!             //need to be changed as well
>>!             char sub = resultSet.sbuf.charAt(resultSet.sbuf.length() - 3);
>>!             if (sub == '+' || sub == '-')
>>              {
>>!                 //we have found timezone info of format +/-HH
>>
>>!                 resultSet.sbuf.setLength(resultSet.sbuf.length() - 3);
>>!                 if (subsecond)
>>!                 {
>>!                     resultSet.sbuf.append('0').append("GMT").append(s.substring(s.length() - 3)).append(":00");
>>!                 }
>>!                 else
>>!                 {
>>!                     resultSet.sbuf.append("GMT").append(s.substring(s.length() - 3)).append(":00");
>>!                 }
>>!             }
>>!             else if (sub == ':')
>>!             {
>>!                 //we may have found timezone info of format +/-HH:MM, or there is no
>>!                 //timezone info at all and this is the : preceding the seconds
>>!                 char sub2 = resultSet.sbuf.charAt(resultSet.sbuf.length() - 5);
>>!                 if (sub2 == '+' || sub2 == '-')
>>                  {
>>!                     //we have found timezone info of format +/-HH:MM
>>!                     resultSet.sbuf.setLength(resultSet.sbuf.length() - 5);
>>!                     if (subsecond)
>>                      {
>>!                         resultSet.sbuf.append('0').append("GMT").append(s.substring(s.length() - 5));
>>!                     }
>>!                     else
>>!                     {
>>!                         resultSet.sbuf.append("GMT").append(s.substring(s.length() - 5));
>>!                     }
>>                  }
>>!                 else if (subsecond)
>>                  {
>>!                     resultSet.sbuf.append('0');
>>                  }
>>-             }
>>-             else if (subsecond)
>>-             {
>>-                 resultSet.sbuf.append('0');
>>-             }
>>-
>>-             // could optimize this a tad to remove too many object creations...
>>-             SimpleDateFormat df = null;
>>
>>!             if (resultSet.sbuf.length() > 23 && subsecond)
>>!             {
>>!                 df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz");
>>!             }
>>!             else if (resultSet.sbuf.length() > 23 && !subsecond)
>>!             {
>>!                 df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz");
>>!             }
>>!             else if (resultSet.sbuf.length() > 10 && subsecond)
>>!             {
>>!                 df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
>>!             }
>>!             else if (resultSet.sbuf.length() > 10 && !subsecond)
>>!             {
>>                  df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
>>              }
>>              else
>>              {
>>                  df = new SimpleDateFormat("yyyy-MM-dd");
>>              }
>>
>>              try
>>              {
>>                  return new Timestamp(df.parse(resultSet.sbuf.toString()).getTime());
>>              }
>>              catch (ParseException e)
>>--- 1591,1709 ----
>>          }
>>      }
>>
>>!     /**
>>!     * Parse a string and return a timestamp representing its value.
>>!     *
>>!     * The driver is set to return ISO date formated strings. We modify this
>>!     * string from the ISO format to a format that Java can understand. Java
>>!     * expects timezone info as 'GMT+09:00' where as ISO gives '+09'.
>>!     * Java also expects fractional seconds to 3 places where postgres
>>!     * will give, none, 2 or 6 depending on the time and postgres version.
>>!     * From version 7.2 postgres returns fractional seconds to 6 places.
>>!     * If available, we drop the last 3 digits.
>>!     *
>>!     * @param s         The ISO formated date string to parse.
>>!     * @param resultSet The ResultSet this date is part of.
>>!     *
>>!     * @return null if s is null or a timestamp of the parsed string s.
>>!     *
>>!     * @throws SQLException if there is a problem parsing s.
>>!     **/
>>!     public static Timestamp toTimestamp(String s, ResultSet resultSet)
>>!     throws SQLException
>>      {
>>          if (s == null)
>>              return null;
>>
>>!         // We must be synchronized here incase more theads access the ResultSet
>>!         // bad practice but possible. Anyhow this is to protect sbuf and
>>!         // SimpleDateFormat objects
>>          synchronized (resultSet)
>>          {
>>!             SimpleDateFormat df = null;
>>
>>!             // If first time, create the buffer, otherwise clear it.
>>              if (resultSet.sbuf == null)
>>                  resultSet.sbuf = new StringBuffer();
>>+             else
>>+                 resultSet.sbuf.setLength(0);
>>
>>!             // Copy s into sbuf for parsing.
>>              resultSet.sbuf.append(s);
>>
>>!             if (s.length() > 19)
>>              {
>>!                 // The len of the ISO string to the second value is 19 chars. If
>>!                 // greater then 19, there should be tz info and perhaps fractional
>>!                 // second info which we need to change to java to read it.
>>!
>>!                 // cut the copy to second value "2001-12-07 16:29:22"
>>!                 int i = 19;
>>!                 resultSet.sbuf.setLength(i);
>>
>>!                 char c = s.charAt(i++);
>>!                 if (c == '.')
>>                  {
>>!                     // Found a fractional value. Append up to 3 digits including
>>!                     // the leading '.'
>>!                     do
>>                      {
>>!                         if (i < 24)
>>!                             resultSet.sbuf.append(c);
>>!                         c = s.charAt(i++);
>>!                     } while (Character.isDigit(c));
>>!
>>!                     // If there wasn't at least 3 digits we should add some zeros
>>!                     // to make up the 3 digits we tell java to expect.
>>!                     for (int j = i; j < 24; j++)
>>!                         resultSet.sbuf.append('0');
>>                  }
>>!                 else
>>                  {
>>!                     // No fractional seconds, lets add some.
>>!                     resultSet.sbuf.append(".000");
>>                  }
>>
>>!                 // prepend the GMT part and then add the remaining bit of
>>!                 // the string.
>>!                 resultSet.sbuf.append(" GMT");
>>!                 resultSet.sbuf.append(c);
>>!                 resultSet.sbuf.append(s.substring(i, s.length()));
>>!
>>!                 // Lastly, if the tz part doesn't specify the :MM part then
>>!                 // we add ":00" for java.
>>!                 if (s.length() - i < 5)
>>!                     resultSet.sbuf.append(":00");
>>!
>>!                 // we'll use this dateformat string to parse the result.
>>!                 df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS z");
>>!             }
>>!             else if (s.length() == 19)
>>!             {
>>!                 // No tz or fractional second info. We could add some, but
>>!                 // then we have to figure out what tz we're in so its easier
>>!                 // to just let Java do that for us. I'm not sure if this is
>>!                 // possible to have to parse a string in this format, as pg
>>!                 // should give us tz qualified timestamps back, but it was
>>!                 // in the old code, so I'm handling it for now.
>>                  df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
>>              }
>>              else
>>              {
>>+                 // We must just a date. Again, I wonder if we have to handle
>>+                 // this case, but it was in the old code and I've no time
>>+                 // to figure it out.
>>                  df = new SimpleDateFormat("yyyy-MM-dd");
>>              }
>>
>>+             // NOTE: if we don't have to handle the last two cases, then
>>+             // we can just initialise df to the first string in a resultset
>>+             // and use that throughout without having to create an object
>>+             // everytime. Leave this for now.
>>+
>>              try
>>              {
>>+                 // All that's left is to parse the string and return the ts.
>>                  return new Timestamp(df.parse(resultSet.sbuf.toString()).getTime());
>>              }
>>              catch (ParseException e)
>>***************
>>*** 1708,1714 ****
>>              }
>>          }
>>      }
>>-
>>-
>>  }
>>
>>--- 1712,1716 ----
>>
>>
>>------------------------------------------------------------------------
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>>http://www.postgresql.org/users-lounge/docs/faq.html
>>