Re: Fix setObject() with Time/Timestamp/Date + Timezones - Mailing list pgsql-jdbc

Patch applied.

thanks,
--Barry

Kim Ho wrote:
> On Mon, 2003-07-07 at 21:01, Barry Lind wrote:
>
>>Kim,
>>
>>I have three issues with this patch.  Although overall I think it is
>>very good work.
>>
>>1)  The methods DateFromString(), TimeFromString() and
>>TimestampFromString() should be named dateFromString(), timeFromString()
>>and timestampFromString() to follow standard java method naming
>>conventions.  If you really want the Date, Time and Timestamp part to
>>remain init caps, might I suggest the following names: toDate(),
>>toTime() and toTimestamp().
>>
>
> Done.
>
>
>>2)  In the TIME and TIMESTAMP cases in the switch, if the object is
>>already of type Time or Timestamp you go ahead and toString() it and
>>then reconvert it back to a Time or Timestamp object.  You should just
>>be using the value as passed.  While I don't know if this will do
>>anything incorrectly, it certainly is a lot more work than necessary.
>>
>
> Done.
>
>
>>3)  I would really like to see some additional test cases added to the
>>test suite for this logic.  Experience has taught me that when it comes
>>to dates and the jdbc driver a good set of tests and expected results is
>>very important.  What I have seen before is patches submitted that
>>change the behavior because someone thinks that the driver is handling
>>timezones offsets incorrectly where as it was really their code that was
>>doing things wrong.  Without a set of tests and expected results it is
>>difficult to know whether such patches are correct or not.
>>
>
> I'll look into this.
>
>
>>thanks,
>>--Barry
>>
>
>
> Cheers,
>
> Kim
>
>
> ------------------------------------------------------------------------
>
> Index: org/postgresql/errors.properties
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/errors.properties,v
> retrieving revision 1.21
> diff -c -p -r1.21 errors.properties
> *** org/postgresql/errors.properties    30 Jun 2003 16:38:30 -0000    1.21
> --- org/postgresql/errors.properties    8 Jul 2003 14:07:24 -0000
> *************** postgresql.call.wrongrtntype:A CallableS
> *** 100,102 ****
> --- 100,105 ----
>   postgresql.input.fetch.gt0:Fetch size must be a value greater than or equal to 0.
>   postgresql.input.query.gt0:Query Timeout must be a value greater than or equal to 0.
>   postgresql.input.rows.gt0:Maximum number of rows must be a value greater than or equal to 0.
> + postgresql.format.baddate:The date given: {0} does not match the format required: {1}.
> + postgresql.format.badtime:The time given: {0} does not match the format required: {1}.
> + postgresql.format.badtimestamp:The timestamp given {0} does not match the format required: {1}.
> Index: org/postgresql/jdbc1/AbstractJdbc1Statement.java
> ===================================================================
> RCS file: /projects/cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Statement.java,v
> retrieving revision 1.26
> diff -c -p -r1.26 AbstractJdbc1Statement.java
> *** org/postgresql/jdbc1/AbstractJdbc1Statement.java    30 Jun 2003 21:10:55 -0000    1.26
> --- org/postgresql/jdbc1/AbstractJdbc1Statement.java    8 Jul 2003 14:07:26 -0000
> *************** public abstract class AbstractJdbc1State
> *** 1488,1500 ****
>                   setString(parameterIndex, x.toString());
>                   break;
>               case Types.DATE:
> !                 setDate(parameterIndex, (java.sql.Date)x);
>                   break;
>               case Types.TIME:
> !                 setTime(parameterIndex, (Time)x);
>                   break;
>               case Types.TIMESTAMP:
> !                 setTimestamp(parameterIndex, (Timestamp)x);
>                   break;
>               case Types.BIT:
>                   if (x instanceof Boolean)
> --- 1488,1518 ----
>                   setString(parameterIndex, x.toString());
>                   break;
>               case Types.DATE:
> !                 if (x instanceof java.sql.Date)
> !                     setDate(parameterIndex, (java.sql.Date)x);
> !                 else
> !                 {
> !                     java.sql.Date tmpd = (x instanceof java.util.Date) ? new
java.sql.Date(((java.util.Date)x).getTime()): dateFromString(x.toString()); 
> !                     setDate(parameterIndex, tmpd);
> !                 }
>                   break;
>               case Types.TIME:
> !                 if (x instanceof java.sql.Time)
> !                     setTime(parameterIndex, (java.sql.Time)x);
> !                 else
> !                 {
> !                     java.sql.Time tmpt = (x instanceof java.util.Date) ? new
java.sql.Time(((java.util.Date)x).getTime()): timeFromString(x.toString()); 
> !                     setTime(parameterIndex, tmpt);
> !                 }
>                   break;
>               case Types.TIMESTAMP:
> !                 if (x instanceof java.sql.Timestamp)
> !                     setTimestamp(parameterIndex ,(java.sql.Timestamp)x);
> !                 else
> !                 {
> !                     java.sql.Timestamp tmpts = (x instanceof java.util.Date) ? new
java.sql.Timestamp(((java.util.Date)x).getTime()): timestampFromString(x.toString()); 
> !                     setTimestamp(parameterIndex, tmpts);
> !                 }
>                   break;
>               case Types.BIT:
>                   if (x instanceof Boolean)
> *************** public abstract class AbstractJdbc1State
> *** 2032,2038 ****
>           return m_useServerPrepare;
>       }
>
> !
>       private static final String PG_TEXT = "text";
>       private static final String PG_INTEGER = "integer";
>       private static final String PG_INT2 = "int2";
> --- 2050,2162 ----
>           return m_useServerPrepare;
>       }
>
> !     private java.sql.Date dateFromString (String s) throws SQLException
> !     {
> !         int timezone = 0;
> !         long millis = 0;
> !         long localoffset = 0;
> !         int timezoneLocation = (s.indexOf('+') == -1) ? s.lastIndexOf("-") : s.indexOf('+');
> !         //if the last index of '-' or '+' is past 8. we are guaranteed that it is a timezone marker
> !         //shortest = yyyy-m-d
> !         //longest = yyyy-mm-dd
> !         try
> !         {
> !             timezone = (timezoneLocation>7) ? timezoneLocation : s.length();
> !             millis = java.sql.Date.valueOf(s.substring(0,timezone)).getTime();
> !         }
> !         catch (Exception e)
> !         {
> !             throw new PSQLException("postgresql.format.baddate",s , "yyyy-MM-dd[-tz]");
> !         }
> !         timezone = 0;
> !         if (timezoneLocation>7 && timezoneLocation+3 == s.length())
> !         {
> !             timezone = Integer.parseInt(s.substring(timezoneLocation+1,s.length()));
> !             localoffset = java.util.Calendar.getInstance().getTimeZone().getOffset(millis);
> !             if (s.charAt(timezoneLocation)=='+')
> !                 timezone*=-1;
> !         }
> !         millis = millis + timezone*60*60*1000 + localoffset;
> !         return new java.sql.Date(millis);
> !     }
> !
> !     private java.sql.Time timeFromString (String s) throws SQLException
> !     {
> !         int timezone = 0;
> !         long millis = 0;
> !         long localoffset = 0;
> !         int timezoneLocation = (s.indexOf('+') == -1) ? s.lastIndexOf("-") : s.indexOf('+');
> !         //if the index of the last '-' or '+' is greater than 0 that means this time has a timezone.
> !         //everything earlier than that position, we treat as the time and parse it as such.
> !         try
> !         {
> !             timezone = (timezoneLocation==-1) ? s.length() : timezoneLocation;
> !             millis = java.sql.Time.valueOf(s.substring(0,timezone)).getTime();
> !         }
> !         catch (Exception e)
> !         {
> !             throw new PSQLException("postgresql.format.badtime",s, "HH:mm:ss[-tz]");
> !         }
> !         timezone = 0;
> !         if (timezoneLocation != -1 && timezoneLocation+3 == s.length())
> !         {
> !             timezone = Integer.parseInt(s.substring(timezoneLocation+1,s.length()));
> !             localoffset = java.util.Calendar.getInstance().getTimeZone().getOffset(millis);
> !             if (s.charAt(timezoneLocation)=='+')
> !                 timezone*=-1;
> !         }
> !         millis = millis + timezone*60*60*1000 + localoffset;
> !         return new java.sql.Time(millis);
> !     }
> !
> !     private java.sql.Timestamp timestampFromString (String s) throws SQLException
> !     {
> !         int timezone = 0;
> !         long millis = 0;
> !         long localoffset = 0;
> !         int nanosVal = 0;
> !         int timezoneLocation = (s.indexOf('+') == -1) ? s.lastIndexOf("-") : s.indexOf('+');
> !         int nanospos = s.indexOf(".");
> !         //if there is a '.', that means there are nanos info, and we take the timestamp up to that point
> !         //if not, then we check to see if the last +/- (to indicate a timezone) is greater than 8
> !         //8 is because the shortest date, will have last '-' at position 7. e.g yyyy-x-x
> !         try
> !         {
> !             if (nanospos != -1)
> !                 timezone = nanospos;
> !             else if (timezoneLocation > 8)
> !                 timezone = timezoneLocation;
> !             else
> !                 timezone = s.length();
> !             millis = java.sql.Timestamp.valueOf(s.substring(0,timezone)).getTime();
> !         }
> !         catch (Exception e)
> !         {
> !             throw new PSQLException("postgresql.format.badtimestamp", s, "yyyy-MM-dd HH:mm:ss[.xxxxxx][-tz]");
> !         }
> !         timezone = 0;
> !         if (nanospos != -1)
> !         {
> !             int tmploc = (timezoneLocation > 8) ? timezoneLocation : s.length();
> !             nanosVal = Integer.parseInt(s.substring(nanospos+1,tmploc));
> !             int diff = 8-((tmploc-1)-(nanospos+1));
> !             for (int i=0;i<diff;i++)
> !                 nanosVal*=10;
> !         }
> !         if (timezoneLocation>8 && timezoneLocation+3 == s.length())
> !         {
> !             timezone = Integer.parseInt(s.substring(timezoneLocation+1,s.length()));
> !             localoffset = java.util.Calendar.getInstance().getTimeZone().getOffset(millis);
> !             if (s.charAt(timezoneLocation)=='+')
> !                 timezone*=-1;
> !         }
> !         millis = millis + timezone*60*60*1000 + localoffset;
> !         java.sql.Timestamp tmpts = new java.sql.Timestamp(millis);
> !         tmpts.setNanos(nanosVal);
> !         return tmpts;
> !     }
> !
> !
>       private static final String PG_TEXT = "text";
>       private static final String PG_INTEGER = "integer";
>       private static final String PG_INT2 = "int2";