Re: Infinity and beyond - Mailing list pgsql-jdbc

> On Thu, 5 May 2005, Donald Fraser wrote:
>
> > A while back I noted in this thread that some people were having issues
> > with timestamps and infinity. I have been using a modified version of
> > the 7.4 driver for some time now and have had no real issues with these
> > modifications. The modifications that I made involve adding two
> > additional classes to the driver package. They are simple classes that
> > extend java.sql.Date and java.sql.Timestamp. In my opinion this solution
> > is much more elegant than relying on some magic number used in the
> > underlying java.util.Date class. The main beauty of these modification
> > are that you do not need to rely any PostgreSQL driver specifics to
> > detect values of infinity, and on a similar note the driver doesn't have
> > to rely on its own implementations of infinity, it can handle third
> > party implementations. If anyone is interested in these modification I
> > will post them on this thread, just ask. Cheers Donald Fraser.

> How can clients not have any reliance on pg driver specifics?  If these
> new classes are added to the driver, won't that just move the dependence
> from PGStatement to PGTimestamp?  Have you seen what the 8.0+ driver
> does for infinity or are you only familiar with 7.4's handling?  In any
> case, I'm interested to see what you have done.

Ok I may have made the assumption that "infinity" was part of the SQL
standards. A quick search suggests that its probably not and therefore a
client cannot assume a similar implementation across different drivers. I
guess I was hoping that it was a standard... :-(
If it was a standard then object.toString() on implementations of timestamp
that support infinity would return the values "infinity" or "-infinity" for
values of +/-infinity respectively. Thus a client wouldn't need to know
anything about the driver, so long as it was aware of the possibility of
infinity values, it could easily detect such values by evaluating
object.toString().
On the same basis the client could make its own implementation of timestamp
that supports infinity, and so long as it complied with the formatting
standard, the driver can detect such implementations using the same
technique outlined above.
This is what I based part of the modifications on - the hope that infinity
was actually a SQL standard.
But the most important issue was to ensure that the implementation of
infinity complied, to the best of its ability, to the Comparable interface.
Thus just using some magic number to represent infinity could easily break
code that orders on Timestamp or Date objects.
Lastly having an object to better represent a data type, in my opinion, is
better programming.

As for being familiar with the 8.0.x driver - I'm always one driver version
behind as I have a number of more significant modifications to the driver
that require patching and unless there are major enhancements I usually
leave it a while, time permitting and all that... Hence I am only familiar
with the 7.4 and earlier versions of the driver. I will endeavour to take a
look at what changes have been made sometime soon.

It would be nice if I could just send you the new class files and the diff.
that would patch my version of the 7.4 driver but considering that there are
a number of more substantial changes, a diff would distract from just
showing the infinity implementation.
Hence I will attach the two classes that represent Date and Timestamp and
include all the code that changed to utilise them - obviously there may be
some major differences between these old 7.4 versions of code and the new
8.0.x versions. I'm sure you'll work them out though.

First of all I realise that PostgreSQL doesn't support infinity for Date,
but I included the implementation for when it does support it.

The changes involve four methods, a summary of which follows:
public static Timestamp toTimestamp(String s, BaseResultSet resultSet,
String pgDataType) in class AbstractJdbc1ResultSet
public static java.sql.Date toDate(String s) throws SQLException in class
AbstractJdbc1ResultSet
public void setTimestamp(int parameterIndex, Timestamp x) throws
SQLException in class AbstractJdbc1Statement
public void setObject(int parameterIndex, Object x, int targetSqlType, int
scale) throws SQLException in class AbstractJdbc1Statement

Actual changes to the above methods are as follows:

**Changes to the class AbstractJdbc1ResultSet**

 public static Timestamp toTimestamp(String s, BaseResultSet resultSet,
String pgDataType)
 throws SQLException
 {
  BaseResultSet rs = resultSet;
  if (s == null)
   return null;

  s = s.trim();
  // 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 (rs)
  {
   StringBuffer l_sbuf = rs.getStringBuffer();
   SimpleDateFormat df = null;
   if ( Driver.logDebug )
    Driver.debug("the data from the DB is " + s);

   // If first time, create the buffer, otherwise clear it.
   if (l_sbuf == null)
    l_sbuf = new StringBuffer(32);
   else
   {
    l_sbuf.setLength(0);
   }

   int slen = s.length();

   // Copy s into sbuf for parsing.
   if (slen >= 19)
    l_sbuf.append(s);


   // For a Timestamp, the fractional seconds are stored in the
   // nanos field. As a DateFormat is used for parsing which can
   // only parse to millisecond precision and which returns a
   // Date object, the fractional second parsing is completely
   // separate.
   int nanos = 0;

   if (slen > 19)
   {
    // The len of the ISO string to the second value is 19 chars. If
    // greater then 19, there may 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;
    l_sbuf.setLength(i);

    char c = s.charAt(i++);
    if (c == '.')
    {
     // Found a fractional value.
     final int start = i;
     while (true)
     {
      c = s.charAt(i++);
      if (!Character.isDigit(c))
       break;
      if (i == slen)
       {
        i++;
        break;
       }
     }

     // The range [start, i - 1) contains all fractional digits.
     final int end = i - 1;
     try
      {
       nanos = Integer.parseInt(s.substring(start, end));
      }
     catch (NumberFormatException e)
      {
       throw new PSQLException("postgresql.unusual",
PSQLState.UNEXPECTED_ERROR, e);
      }

     // The nanos field stores nanoseconds. Adjust the parsed
     // value to the correct magnitude.
     for (int digitsToNano = 9 - (end - start);
       digitsToNano > 0; --digitsToNano)
      nanos *= 10;
    }

    if (i < slen)
    {
     // prepend the GMT part and then add the remaining bit of
     // the string.
     l_sbuf.append(" GMT");
     l_sbuf.append(c);
     l_sbuf.append(s.substring(i, slen));

     // Lastly, if the tz part doesn't specify the :MM part then
     // we add ":00" for java.
     if (slen - i < 5)
      l_sbuf.append(":00");

     // we'll use this dateformat string to parse the result.
     df = rs.getTimestampTZFormat();
    }
    else
    {
     // Just found fractional seconds but no timezone.
     //If timestamptz then we use GMT, else local timezone
     if (pgDataType.equals("timestamptz"))
     {
      l_sbuf.append(" GMT");
      df = rs.getTimestampTZFormat();
     }
     else
     {
      df = rs.getTimestampFormat();
     }
    }
   }
   else if (slen == 19)
   {
    // No tz or fractional second info.
    //If timestamptz then we use GMT, else local timezone
    if (pgDataType.equals("timestamptz"))
    {
     l_sbuf.append(" GMT");
     df = rs.getTimestampTZFormat();
    }
    else
    {
     df = rs.getTimestampFormat();
    }
   }
   else
   {
    if (slen == 8 && s.equals("infinity"))
     //java doesn't have a concept of postgres's infinity
     //so set to our PGTimestamp
     return new PGTimestamp(true);
    if (slen == 9 && s.equals("-infinity"))
     //java doesn't have a concept of postgres's infinity
     //so set to our PGTimestamp
     return new PGTimestamp(false);

    // We must just have a date. This case is
    // needed if this method is called on a date
    // column
    df = rs.getDateFormat();
    l_sbuf.append(s);
   }

   try
   {
    // All that's left is to parse the string and return the ts.
    if ( Driver.logDebug )
     Driver.debug("the data after parsing is "
                     + l_sbuf.toString() + " with " + nanos + " nanos");

     Timestamp result = new
PGTimestamp(df.parse(l_sbuf.toString()).getTime());
    result.setNanos(nanos);
    return result;
   }
   catch (ParseException e)
   {
    throw new PSQLException("postgresql.res.badtimestamp",
PSQLState.BAD_DATETIME_FORMAT, new Integer(e.getErrorOffset()), s);
   }
  }
 }

 public static java.sql.Date toDate(String s) throws SQLException
 {
  if (s == null)
   return null;
  // length == 10: SQL Date
  // length >  10: SQL Timestamp, assumes PGDATESTYLE=ISO
  if (s.compareTo("-infinity") == 0)
   return new PGDate(false);

  if (s.compareTo("infinity") == 0)
   return new PGDate(true);
  try
  {
   s = s.trim().substring(0, 10);
   // we could just return a straight java.sql.Date object here but
   // it would be more consistant to always return PGDate
   return new PGDate((java.sql.Date.valueOf(s).getTime()));
  }
  catch (NumberFormatException e)
  {
   throw new
PSQLException("postgresql.res.baddate",PSQLState.BAD_DATETIME_FORMAT, s);
  }
 }

**Changes to the class AbstractJdbc1Statement **

 public void setTimestamp(int parameterIndex, Timestamp x) throws
SQLException
 {
  if (null == x)
  {
   setNull(parameterIndex, Types.TIMESTAMP);
  }
  else
  {
   // Use the shared StringBuffer
   synchronized (sbuf)
   {
    sbuf.setLength(0);
    sbuf.ensureCapacity(32);
    sbuf.append("'");
    String sRawValue = x.toString().toLowerCase();
    if (sRawValue.indexOf("infinity") >= 0)
    {
     sbuf.append(sRawValue.trim());
    }
    else
    {

     //format the timestamp
     //we do our own formating so that we can get a format
     //that works with both timestamp with time zone and
     //timestamp without time zone datatypes.
     //The format is '2002-01-01 23:59:59.123456-0130'
     //we need to include the local time and timezone offset
     //so that timestamp without time zone works correctly
     int l_year = x.getYear() + 1900;

     // always use four digits for the year so very
     // early years, like 2, don't get misinterpreted
     int l_yearlen = String.valueOf(l_year).length();
     for (int i=4; i>l_yearlen; i--) {
      sbuf.append("0");
     }

     sbuf.append(l_year);
     sbuf.append('-');
     int l_month = x.getMonth() + 1;
     if (l_month < 10)
      sbuf.append('0');
     sbuf.append(l_month);
     sbuf.append('-');
     int l_day = x.getDate();
     if (l_day < 10)
      sbuf.append('0');
     sbuf.append(l_day);
     sbuf.append(' ');
     int l_hours = x.getHours();
     if (l_hours < 10)
      sbuf.append('0');
     sbuf.append(l_hours);
     sbuf.append(':');
     int l_minutes = x.getMinutes();
     if (l_minutes < 10)
      sbuf.append('0');
     sbuf.append(l_minutes);
     sbuf.append(':');
     int l_seconds = x.getSeconds();
     if (l_seconds < 10)
      sbuf.append('0');
     sbuf.append(l_seconds);
     // Make decimal from nanos.
     char[] l_decimal = {'0', '0', '0', '0', '0', '0', '0', '0', '0'};
     char[] l_nanos = Integer.toString(x.getNanos()).toCharArray();
     System.arraycopy(l_nanos, 0, l_decimal, l_decimal.length -
l_nanos.length, l_nanos.length);
     sbuf.append('.');
     if (connection.haveMinimumServerVersion("7.2"))
     {
      sbuf.append(l_decimal, 0, 6);
     }
     else
     {
      // Because 7.1 include bug that "hh:mm:59.999" becomes "hh:mm:60.00".
      sbuf.append(l_decimal, 0, 2);
     }
     //add timezone offset
     int l_offset = -(x.getTimezoneOffset());
     int l_houros = l_offset / 60;
     if (l_houros >= 0)
     {
      sbuf.append('+');
     }
     else
     {
      sbuf.append('-');
     }
     if (l_houros > -10 && l_houros < 10)
      sbuf.append('0');
     if (l_houros >= 0)
     {
      sbuf.append(l_houros);
     }
     else
     {
      sbuf.append(-l_houros);
     }
     int l_minos = l_offset - (l_houros * 60);
     if (l_minos != 0)
     {
      if (l_minos > -10 && l_minos < 10)
       sbuf.append('0');
      if (l_minos >= 0)
      {
       sbuf.append(l_minos);
      }
      else
      {
       sbuf.append(-l_minos);
      }
     }
    }
    sbuf.append("'");
    bind(parameterIndex, sbuf.toString(), PG_TIMESTAMPTZ);
   }

  }
 }

 public void setObject(int parameterIndex, Object x, int targetSqlType, int
scale) throws SQLException
 {
  if (x == null)
  {
   setNull(parameterIndex, targetSqlType);
   return ;
  }
  switch (targetSqlType)
  {
   case Types.INTEGER:
    bind(parameterIndex, numericValueOf(x), PG_INTEGER);
    break;
   case Types.TINYINT:
   case Types.SMALLINT:
    bind(parameterIndex, numericValueOf(x), PG_INT2);
    break;
   case Types.BIGINT:
    bind(parameterIndex, numericValueOf(x), PG_INT8);
    break;
   case Types.REAL:
   case Types.FLOAT:
    bind(parameterIndex, numericValueOf(x), PG_FLOAT);
    break;
   case Types.DOUBLE:
    bind(parameterIndex, numericValueOf(x), PG_DOUBLE);
    break;
   case Types.DECIMAL:
   case Types.NUMERIC:
    bind(parameterIndex, numericValueOf(x), PG_NUMERIC);
    break;
   case Types.CHAR:
   case Types.VARCHAR:
   case Types.LONGVARCHAR:
    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
    {
     if (x.toString().indexOf("infinity") >= 0)
     {
      bind(parameterIndex, "'" + x.toString() + "'", PG_TIMESTAMPTZ);
     }
     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)
    {
     bind(parameterIndex, ((Boolean)x).booleanValue() ? "'1'" : "'0'",
PG_BOOLEAN);
    }
    else if (x instanceof String)
    {
     bind(parameterIndex, Boolean.valueOf(x.toString()).booleanValue() ?
"'1'" : "'0'", PG_BOOLEAN);
    }
    else if (x instanceof Number)
    {
     bind(parameterIndex, ((Number)x).intValue()!=0 ? "'1'" : "'0'",
PG_BOOLEAN);
    }
    else
    {
     throw new PSQLException("postgresql.prep.type",
PSQLState.INVALID_PARAMETER_TYPE);
    }
    break;
   case Types.BINARY:
   case Types.VARBINARY:
   case Types.LONGVARBINARY:
    setObject(parameterIndex, x);
    break;
   case Types.OTHER:
    if (x instanceof PGobject)
     setString(parameterIndex, ((PGobject)x).getValue(),
((PGobject)x).getType());
    else
     throw new PSQLException("postgresql.prep.type",
PSQLState.INVALID_PARAMETER_TYPE);
    break;
   default:
    throw new PSQLException("postgresql.prep.type",
PSQLState.INVALID_PARAMETER_TYPE);
  }
 }


If my modifications aren't of any use no worries... In the mean time I'll
take a look around the 8.0.x driver
Cheers
Donald Fraser