Skip site navigation (1) Skip section navigation (2)

Re: patch against cvs for getTimestamp() problem.

From: Barry Lind <barry(at)xythos(dot)com>
To: "Thomas O'Dowd" <tom(at)nooper(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: patch against cvs for getTimestamp() problem.
Date: 2001-12-08 17:00:22
Message-ID: 3C124726.6020304@xythos.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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
> 



In response to

Responses

pgsql-jdbc by date

Next:From: Antonio Fiol BonnĂ­nDate: 2001-12-08 18:50:04
Subject: Re: Bug with caching SQLTypes in Connection:getSQLType(oid)
Previous:From: Rene PijlmanDate: 2001-12-08 13:25:23
Subject: Re: Bug with caching SQLTypes in Connection:getSQLType(oid)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group