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

Re: [PATCHES] Patch for JDBC timestamp problems

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: pgman(at)candle(dot)pha(dot)pa(dot)us
Cc: Barry Lind <barry(at)xythos(dot)com>, pgsql-patches(at)postgresql(dot)org, pgsql-interfaces(at)postgresql(dot)org
Subject: Re: [PATCHES] Patch for JDBC timestamp problems
Date: 2001-01-13 18:53:17
Message-ID: 200101131853.NAA20477@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-interfacespgsql-patches
I have backed out this patch because of concerns that have been raised. 
I would be glad to re-apply it after these issues have been addressed.


> I have applied this patch.  It fixes a number of bugs, even one that was
> just reported a few day ago.  Thanks.
> 
> 
> ---------------------------------------------------------------------------
> 
> 
> Attached is a set of patches for a couple of bugs dealing with
> timestamps in JDBC.
> 
> Bug#1) Incorrect timestamp stored in DB if client timezone different
> than DB.
> 
> The buggy implementation of setTimestamp() in PreparedStatement simply
> used the toString() method of the java.sql.Timestamp object to convert
> to a string to send to the database.  The format of this is yyyy-MM-dd
> hh:mm:ss.SSS which doesn't include any timezone information.  Therefore
> the DB assumes its timezone since none is specified.  That is OK if the
> timezone of the client and server are the same, however if they are
> different the wrong timestamp is received by the server.  For example if
> the client is running in timezone GMT and wants to send the timestamp
> for noon to a server running in PST (GMT-8 hours), then the server will
> receive 2000-01-12 12:00:00.0 and interprete it as 2000-01-12
> 12:00:00-08 which is 2000-01-12 04:00:00 in GMT.  The fix is to send a
> format to the server that includes the timezone offset.  For simplicity
> sake the fix uses a SimpleDateFormat object with its timezone set to GMT
> so that '+00' can be used as the timezone for postgresql.  This is done
> as SimpleDateFormat doesn't support formating timezones in the way
> postgresql expects.
> 
> Bug#2) Incorrect handling of partial seconds in getting timestamps from
> the DB
> 
> When the SimpleDateFormat object parses a string with a format like
> yyyy-MM-dd hh:mm:ss.SS it expects the fractional seconds to be three
> decimal places (time precision in java is miliseconds = three decimal
> places).  This seems like a bug in java to me, but it is unlikely to be
> fixed anytime soon, so the postgresql code needed modification to
> support the java behaviour.  So for example a string of '2000-01-12
> 12:00:00.12-08' coming from the database was being converted to a
> timestamp object with a value of 2000-01-12 12:00:00.012GMT-08:00.  The
> fix was to check for a '.' in the string and if one is found append on
> an extra zero to the fractional seconds part.
> 
> Bug#3) Performance problems
> 
> In fixing the above two bugs, I noticed some things that could be
> improved.  In PreparedStatement.setTimestamp(),
> PreparedStatement.setDate(), ResultSet.getTimestamp(), and
> ResultSet.getDate() these methods were creating a new SimpleDateFormat
> object everytime they were called.  To avoid this unnecessary object
> creation overhead, I changed the code to use static variables for
> keeping a single instance of the needed formating objects.
> Also the code used the + operator for string concatenation.  As everyone
> should know this is very inefficient and the use of StringBuffers is
> prefered.
> 
> I also did some cleanup in ResultSet.getTimestamp().  This method has
> had multiple patches applied some of which resulted in code that was no
> longer needed.  For example the ISO timestamp format that postgresql
> uses specifies the timezone as an offset like '-08'.  Code was added at
> one point to convert the postgresql format to the java one which is
> GMT-08:00, however the old code was left around which did nothing.  So
> there was code that looked for yyyy-MM-dd hh:mm:sszzzzzzzzz and
> yyyy-MM-dd hh:mm:sszzz.  This second format would never be encountered
> because zzz (i.e. -08) would be converted into the former (also note
> that the SimpleDateFormat object treats zzzzzzzzz and zzz the same, the
> number of z's does not matter).
> 
> 
> There was another problem/fix mentioned on the email lists today by
> mcannon(at)internet(dot)com which is also fixed by this patch:
> 
> Bug#4) Fractional seconds lost when getting timestamp from the DB
> A patch by Jan Thomea handled the case of yyyy-MM-dd hh:mm:sszzzzzzzzz
> but not the fractional seconds version yyyy-MM-dd hh:mm:ss.SSzzzzzzzzz. 
> The code is fixed to handle this case as well.
> 
> thanks,
> --Barry
> --------------F902A91CC78EA5B0218A576D
> Content-Type: text/plain; charset=UTF-8; name="patch.diff"
> Content-Transfer-Encoding: 7bit
> Content-Disposition: inline; filename="patch.diff"
> 
> *** interfaces/jdbc/org/postgresql/jdbc1/PreparedStatement.java.orig	Fri Jan 12 17:17:48 2001
> --- interfaces/jdbc/org/postgresql/jdbc1/PreparedStatement.java	Fri Jan 12 17:27:28 2001
> ***************
> *** 310,321 ****
>   	 * @param x the parameter value
>   	 * @exception SQLException if a database access error occurs
>   	 */
>   	public void setDate(int parameterIndex, java.sql.Date x) throws SQLException
>   	{
> ! 	  SimpleDateFormat df = new SimpleDateFormat("''yyyy-MM-dd''");
> ! 	  
> ! 	  set(parameterIndex, df.format(x));
> ! 	  
>   	  // The above is how the date should be handled.
>   	  //
>   	  // However, in JDK's prior to 1.1.6 (confirmed with the
> --- 310,320 ----
>   	 * @param x the parameter value
>   	 * @exception SQLException if a database access error occurs
>   	 */
> +         private static final SimpleDateFormat DF1 = new SimpleDateFormat("yyyy-MM-dd");
>   	public void setDate(int parameterIndex, java.sql.Date x) throws SQLException
>   	{
> ! 	  set(parameterIndex, DF1.format(x));
> ! 
>   	  // The above is how the date should be handled.
>   	  //
>   	  // However, in JDK's prior to 1.1.6 (confirmed with the
> ***************
> *** 349,357 ****
>   	 * @param x the parameter value
>   	 * @exception SQLException if a database access error occurs
>   	 */
>   	public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException
> ! 	{
> ! 		set(parameterIndex, "'" + x.toString() + "'");
>   	}
>   
>   	/**
> --- 348,364 ----
>   	 * @param x the parameter value
>   	 * @exception SQLException if a database access error occurs
>   	 */
> +         private static SimpleDateFormat DF2 = getDF2();
> +         private static SimpleDateFormat getDF2() {
> +           SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
> +           sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
> +           return sdf;
> +         }
>   	public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException
> !         {
> !             StringBuffer strBuf = new StringBuffer("'");
> !             strBuf.append(DF2.format(x)).append('.').append(x.getNanos()/10000000).append("+00'");
> ! 	    set(parameterIndex, strBuf.toString());
>   	}
>   
>   	/**
> *** interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java.orig	Fri Jan 12 17:18:45 2001
> --- interfaces/jdbc/org/postgresql/jdbc1/ResultSet.java	Fri Jan 12 17:25:02 2001
> ***************
> *** 412,420 ****
>       String s = getString(columnIndex);
>       if(s==null)
>         return null;
> -     SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
>       try {
> !       return new java.sql.Date(df.parse(s).getTime());
>       } catch (ParseException e) {
>         throw new PSQLException("postgresql.res.baddate",new Integer(e.getErrorOffset()),s);
>       }
> --- 412,419 ----
>       String s = getString(columnIndex);
>       if(s==null)
>         return null;
>       try {
> !       return new java.sql.Date(DF5.parse(s).getTime());
>       } catch (ParseException e) {
>         throw new PSQLException("postgresql.res.baddate",new Integer(e.getErrorOffset()),s);
>       }
> ***************
> *** 457,486 ****
>      * @return the column value; null if SQL NULL
>      * @exception SQLException if a database access error occurs
>      */
>     public Timestamp getTimestamp(int columnIndex) throws SQLException
>     {
>       String s = getString(columnIndex);
>       if(s==null)
>   	return null;
> !     
> !     // This works, but it's commented out because Michael Stephenson's
> !     // solution is better still:
> !     //SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
> !     
> !     // Michael Stephenson's solution:
>       SimpleDateFormat df = null;
> !     if (s.length()>21 && s.indexOf('.') != -1) {
> ! 	df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzz");
> !     } else if (s.length()>19 && s.indexOf('.') == -1) {
> ! 	df = new SimpleDateFormat("yyyy-MM-dd HH:MM:sszzz");
> !     } else if (s.length()>19 && s.indexOf('.') != -1) {
> ! 	df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss.SS");
> !     } else if (s.length()>10 && s.length()<=18) {
> ! 	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) {
> --- 456,514 ----
>      * @return the column value; null if SQL NULL
>      * @exception SQLException if a database access error occurs
>      */
> +   private static final SimpleDateFormat DF1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz");
> +   private static final SimpleDateFormat DF2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz");
> +   private static final SimpleDateFormat DF3 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
> +   private static final SimpleDateFormat DF4 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
> +   private static final SimpleDateFormat DF5 = new SimpleDateFormat("yyyy-MM-dd");
>     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);
> !     char sub = strBuf.charAt(strBuf.length()-3);
> !     if (sub == '+' || sub == '-') {
> !       strBuf.setLength(strBuf.length()-3);
> !       if (subsecond)  {
> !         strBuf = strBuf.append('0').append("GMT").append(s.substring(s.length()-3, s.length())).append(":00");
> !       } else {
> !         strBuf = strBuf.append("GMT").append(s.substring(s.length()-3, s.length())).append(":00");
> !       }
> !     } else if (subsecond) {
> !       strBuf = strBuf.append('0');
> !     }
> ! 
> !     s = strBuf.toString();
> ! 
>       SimpleDateFormat df = null;
> ! 
> !     if (s.length()>23 && subsecond) {
> !       df = DF1;
> !     } else if (s.length()>23 && !subsecond) {
> !       df = DF2;
> !     } else if (s.length()>10 && subsecond) {
> !       df = DF3;
> !     } else if (s.length()>10 && !subsecond) {
> !       df = DF4;
>       } else {
> !       df = DF5;
>       }
> ! 
>       try {
>   	return new Timestamp(df.parse(s).getTime());
>       } catch(ParseException e) {
> *** interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java.orig	Fri Jan 12 17:40:55 2001
> --- interfaces/jdbc/org/postgresql/jdbc2/PreparedStatement.java	Fri Jan 12 17:47:42 2001
> ***************
> *** 310,321 ****
>   	 * @param x the parameter value
>   	 * @exception SQLException if a database access error occurs
>   	 */
>   	public void setDate(int parameterIndex, java.sql.Date x) throws SQLException
>   	{
> ! 	  SimpleDateFormat df = new SimpleDateFormat("''yyyy-MM-dd''");
> ! 	  
> ! 	  set(parameterIndex, df.format(x));
> ! 	  
>   	  // The above is how the date should be handled.
>   	  //
>   	  // However, in JDK's prior to 1.1.6 (confirmed with the
> --- 310,320 ----
>   	 * @param x the parameter value
>   	 * @exception SQLException if a database access error occurs
>   	 */
> +         private static final SimpleDateFormat DF1 = new SimpleDateFormat("yyyy-MM-dd");
>   	public void setDate(int parameterIndex, java.sql.Date x) throws SQLException
>   	{
> ! 	  set(parameterIndex, DF1.format(x));
> ! 
>   	  // The above is how the date should be handled.
>   	  //
>   	  // However, in JDK's prior to 1.1.6 (confirmed with the
> ***************
> *** 349,357 ****
>   	 * @param x the parameter value
>   	 * @exception SQLException if a database access error occurs
>   	 */
>   	public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException
> ! 	{
> ! 		set(parameterIndex, "'" + x.toString() + "'");
>   	}
>   
>   	/**
> --- 348,364 ----
>   	 * @param x the parameter value
>   	 * @exception SQLException if a database access error occurs
>   	 */
> +         private static SimpleDateFormat DF2 = getDF2();
> +         private static SimpleDateFormat getDF2() {
> +           SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
> +           sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
> +           return sdf;
> +         }
>   	public void setTimestamp(int parameterIndex, Timestamp x) throws SQLException
> !         {
> !             StringBuffer strBuf = new StringBuffer("'");
> !             strBuf.append(DF2.format(x)).append('.').append(x.getNanos()/10000000).append("+00'");
> ! 	    set(parameterIndex, strBuf.toString());
>   	}
>   
>   	/**
> *** interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java.orig	Fri Jan 12 16:47:28 2001
> --- interfaces/jdbc/org/postgresql/jdbc2/ResultSet.java	Fri Jan 12 17:03:03 2001
> ***************
> *** 415,423 ****
>       String s = getString(columnIndex);
>       if(s==null)
>         return null;
> -     SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
>       try {
> !       return new java.sql.Date(df.parse(s).getTime());
>       } catch (ParseException e) {
>         throw new PSQLException("postgresql.res.baddate",new Integer(e.getErrorOffset()),s);
>       }
> --- 415,422 ----
>       String s = getString(columnIndex);
>       if(s==null)
>         return null;
>       try {
> !       return new java.sql.Date(DF5.parse(s).getTime());
>       } catch (ParseException e) {
>         throw new PSQLException("postgresql.res.baddate",new Integer(e.getErrorOffset()),s);
>       }
> ***************
> *** 460,506 ****
>      * @return the column value; null if SQL NULL
>      * @exception SQLException if a database access error occurs
>      */
>     public Timestamp getTimestamp(int columnIndex) throws SQLException
>     {
>       String s = getString(columnIndex);
>       if(s==null)
>   	return null;
> !     
> !     // This works, but it's commented out because Michael Stephenson's
> !     // solution is better still:
> !     //SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
> ! // Modification by Jan Thomae
> !     String sub = s.substring(s.length() - 3, s.length()-2);
> !     if (sub.equals("+") || sub.equals("-")) {
> !             s = s.substring(0, s.length()-3) + "GMT"+ s.substring(s.length()-3, s.length())+":00";
>       }
> ! // -------
> !        // Michael Stephenson's solution:
>       SimpleDateFormat df = null;
>   
> ! // Modification by Jan Thomae
> !     if (s.length()>27) {
> !     df = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz");  
> !     } else              
> ! // -------
> !     if (s.length()>21 && s.indexOf('.') != -1) {
> ! 	df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSzzz");
> !     } else if (s.length()>19 && s.indexOf('.') == -1) {
> ! 	df = new SimpleDateFormat("yyyy-MM-dd HH:MM:sszzz");
> !     } else if (s.length()>19 && s.indexOf('.') != -1) {
> ! 	df = new SimpleDateFormat("yyyy-MM-dd HH:MM:ss.SS");
> !     } else if (s.length()>10 && s.length()<=18) {
> ! 	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) {
>   	throw new PSQLException("postgresql.res.badtimestamp",new Integer(e.getErrorOffset()),s);
>       }
>     }
>     
>     /**
>      * A column value can be retrieved as a stream of ASCII characters
> --- 459,524 ----
>      * @return the column value; null if SQL NULL
>      * @exception SQLException if a database access error occurs
>      */
> +   private static final SimpleDateFormat DF1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSzzzzzzzzz");
> +   private static final SimpleDateFormat DF2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:sszzzzzzzzz");
> +   private static final SimpleDateFormat DF3 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
> +   private static final SimpleDateFormat DF4 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
> +   private static final SimpleDateFormat DF5 = new SimpleDateFormat("yyyy-MM-dd");
>     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);
> !     char sub = strBuf.charAt(strBuf.length()-3);
> !     if (sub == '+' || sub == '-') {
> !       strBuf.setLength(strBuf.length()-3);
> !       if (subsecond)  {
> !         strBuf = strBuf.append('0').append("GMT").append(s.substring(s.length()-3, s.length())).append(":00");
> !       } else {
> !         strBuf = strBuf.append("GMT").append(s.substring(s.length()-3, s.length())).append(":00");
> !       }
> !     } else if (subsecond) {
> !       strBuf = strBuf.append('0');
>       }
> ! 
> !     s = strBuf.toString();
> ! 
>       SimpleDateFormat df = null;
>   
> !     if (s.length()>23 && subsecond) {
> !       df = DF1;
> !     } else if (s.length()>23 && !subsecond) {
> !       df = DF2;
> !     } else if (s.length()>10 && subsecond) {
> !       df = DF3;
> !     } else if (s.length()>10 && !subsecond) {
> !       df = DF4;
>       } else {
> !       df = DF5;
>       }
> ! 
>       try {
>   	return new Timestamp(df.parse(s).getTime());
>       } catch(ParseException e) {
>   	throw new PSQLException("postgresql.res.badtimestamp",new Integer(e.getErrorOffset()),s);
>       }
>     }
> + 
>     
>     /**
>      * A column value can be retrieved as a stream of ASCII characters
> 
> --------------F902A91CC78EA5B0218A576D--
> 
> 
> 
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman(at)candle(dot)pha(dot)pa(dot)us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

pgsql-patches by date

Next:From: Barry LindDate: 2001-01-13 21:41:57
Subject: Re: [PATCHES] Patch for JDBC timestamp problems
Previous:From: The Hermit HackerDate: 2001-01-13 18:46:11
Subject: Re: [PATCHES] Patch for JDBC timestamp problems

pgsql-interfaces by date

Next:From: Barry LindDate: 2001-01-13 21:41:57
Subject: Re: [PATCHES] Patch for JDBC timestamp problems
Previous:From: The Hermit HackerDate: 2001-01-13 18:46:11
Subject: Re: [PATCHES] Patch for JDBC timestamp problems

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