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

Thomas,

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

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

thanks,
--Barry

Thomas O'Dowd wrote:

> As a followup to this I noticed a small typo in the jdbc1 part of the
> patch which is fixed in this patch. So please ignore the first patch
> that I sent. I'd like to add that the jdbc2 part is tested as far as
> I can but someone should briefly test the jdbc1 part as I haven't
> the environment to test that here.
>
> Regards,
>
> Tom.
>
> On Sat, Dec 08, 2001 at 02:40:51PM +0900, Thomas O'Dowd wrote:
>
>>Hi all,
>>
>>I just upgraded my development environment to 7.2b3 and the latest cvs
>>driver both built from source. I found that ResultSet.getTimestamp()
>>was no longer working against a 7.2 database and causing an exception
>>parsing the timestamp that pg was returning.
>>
>>Bad Timestamp Format at 23 in 2001-12-06 23:24:07.895882+09
>> at org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1707)
>> at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398)
>>
>>It seems that timestamps have become more accurate in 7.2 going from
>>2 fractional second digits to 6 which was causing the exception. Looking
>>at the code in toTimestamp() I decided that it was better to rewrite
>>it, then add a small fix as the method of parsing before was error
>>prone in my opinion and the new code (less the comments) is smaller
>>and easier to read IMHO :)
>>
>>I'm including patches against jdbc1 and jdbc2 ResultSet.java. I generated
>>the patch against the latest cvs, using cvs diff -c. I've tested the
>>jdbc2 versions pretty well and it parses the following combinations
>>
>>"2001-12-07 16:29:22.47+09",
>>"2001-12-07 16:29:26+09",
>>"2001-12-07 16:26:50.144213+09",
>>"2001-12-07 16:26:59.461349+09",
>>"2001-12-07 16:26:59.461349+05:30",
>>"2001-12-07 00:00:00+09",
>>"2001-12-07 12:00:00",
>>"2001-12-07"
>>
>>The driver is set to return ISO datestyle formats so I'm not sure if the
>>latter two formats are required but as the older code supported them, I've
>>added code to do this. If anyone can suggest more formats that pg may
>>return I can test those on the new code too. I've been running the new
>>driver with my code with no problem now.
>>
>>If anyone has any questions or suggestions please let me know. I think
>>this is an important bug fix for the 7.2 release of pg. Can someone test
>>the changes in the jdbc1 driver as I don't have a means of doing this.
>>
>>The patch file is attached.
>>
>>Regards,
>>
>>Tom.
>>--
>>Thomas O'Dowd. - Nooping - http://nooper.com
>>tom(at)nooper(dot)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
>>