Обсуждение: patch against cvs for getTimestamp() problem.
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
Вложения
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
Вложения
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
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, 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 >>
Hi Barry, This sounds good. I've been running the new patch continously with 4 different java server programs (around 20,000 lines of code) which rely alot on timestamps with 7.2b3 for the last couple of days. The code was failing with the unpatched driver (and 7.2b3), so I'm confident that the change is necessary for the 7.2 release. If its in cvs, I'll update and test the changes again. Tom. On Mon, Dec 10, 2001 at 09:12:42PM -0800, Barry Lind wrote: > 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. - Nooping - http://nooper.com tom@nooper.com - Testing - http://nooper.co.jp/labs