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

Re: (2) 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: (2) patch against cvs for getTimestamp() problem.
Date: 2001-12-11 05:12:42
Message-ID: 3C1595CA.3010209@xythos.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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
>>



In response to

Responses

pgsql-jdbc by date

Next:From: Barry LindDate: 2001-12-11 05:18:27
Subject: Re: JDBC 3.0 / JDK 1.4 build issues
Previous:From: Barry LindDate: 2001-12-11 05:03:25
Subject: Re: Bug with caching SQLTypes in Connection:getSQLType(oid)

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