Re: patch against cvs for getTimestamp() problem.

Hi Barry,

Thanks for the reply. I just tested this with the current patch and
it works. The patch supports this and any number of fractional
digits. It works by either building the number of digits to the 3
that java expects or by throwing away digits after 3. It doesn't do
rounding.

Cheers,

Tom.

On Sat, Dec 08, 2001 at 09:00:22AM -0800, Barry Lind wrote:
> Thomas,
>
> The following formats also need to be supported:
>
> "2001-12-07 16:26:59.4613+09",
> "2001-12-07 16:26:59.4613+09:30",
>
> and possibly any number of digits of fractional seconds should be
> supported, but last I checked the backend output two at a time (i.e.
> either .99, .9999, or .999999)
>
> I will look at the patch in detail later this weekend.
>
> thanks,
> --Barry
>
>
>
>
> 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.
> >
> >
> > ------------------------------------------------------------------------
> >
> > 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(resultSet.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 4: Don't 'kill -9' the postmaster
> >
>
>

--
Thomas O'Dowd. - Nooping - http://nooper.com
tom@nooper.com - Testing - http://nooper.co.jp/labs