Re: patch against cvs for getTimestamp() problem.

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
>