db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [PATCH] Timestamp Arithmetic
Date Wed, 18 May 2005 21:36:46 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
TimestampAdd seems to allow adding non-integer intervals, but the
behavior doesn't seem right. Either the result should be "10:10:20.9"
or the statement should error. Though JDBC documenation is not very
clear, I thought the "count" intervals need to be an integer?<br>
<br>
ij&gt; values {fn timestampadd(SQL_TSI_SECOND, 10.9, time('10:10:10'))};<br>
1<br>
--------------------------<br>
2005-05-18 10:10:20.0<br>
<br>
If we only allow integers for count, then the following needs to change:<br>
<br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if( ! bindParameter( rightOperand, Types.INTEGER))<br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {<br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if( ! rightOperand.getTypeId().isNumericTypeId())<br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; throw
StandardException.newException(SQLState.LANG_INVALID_FUNCTION_ARG_TYPE,<br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
rightOperand.getTypeId().getSQLTypeName(),<br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
ReuseFactory.getInteger(2),<br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; operator);<br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<br>
<br>
Satheesh<br>
<br>
Jack Klebanoff wrote:<br>
<blockquote cite="mid428B61FA.5000608@sbcglobal.net" type="cite">I have
attached a new patch that addresses some of Dan's concerns. The new
patch uses the CURRENT_DATE mechanism to ensure that the same date is
used throughout the lifetime of a statement. It puts the timestamp
arithmetic compilation in the TernaryOperatorNode class to avoid adding
another class to Derby's footprint. Thank you, Satheesh Bandaram, for
suggesting TernaryOperatorNode.
  <br>
  <br>
The patch passed the derbyall test suite.
  <br>
  <br>
svn status is:
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\NodeFactoryImpl.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\TernaryOperatorNode.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj
  <br>
?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\impl\services\monitor\AntiGC.class
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\iapi\sql\compile\C_NodeTypes.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\iapi\types\DateTimeDataValue.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\iapi\types\SQLDate.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\iapi\types\SQLTime.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\iapi\types\SQLTimestamp.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\iapi\reference\SQLState.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\loc\messages_en.properties
  <br>
A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\tests\lang\timestampArith.java
  <br>
A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\master\timestampArith.out
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\suites\derbylang.runall
  <br>
  <br>
Further replies are embedded below.
  <br>
  <br>
Jack
  <br>
  <br>
Daniel John Debrunner wrote:
  <br>
  <br>
  <blockquote type="cite">Jack Klebanoff wrote:
    <br>
    <br>
    <br>
&nbsp;
    <br>
    <br>
    <blockquote type="cite">My copy of the JDBC 3.0 spec does not say
what TIMESTAMPADD and
      <br>
TIMESTAMPDIFF are supposed to do when the input is a date or time.
      <br>
However the ODBC spec on Microsoft's web site specifies the above
      <br>
behavior. My understanding is that JDBC follows ODBC here.
      <br>
&nbsp;&nbsp; <br>
    </blockquote>
    <br>
Dumb question, but did you see TIMESTAMPADD in the ODBC spec?
    <br>
    <br>
Here I only see TIMESTAMPDIFF
    <br>
    <br>
<a class="moz-txt-link-freetext" href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbctime__date__and_interval_functions.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbctime__date__and_interval_functions.asp</a>
    <br>
    <br>
    <br>
&nbsp;
    <br>
    <br>
  </blockquote>
I think that Microsoft made a mistake in formatting that page. The
"TIMESTAMPADD" title cell is missing from the table, but the table does
describe the TIMESTAMPADD function. It is tacked on to the end of the
description of the "SECOND" function.
  <br>
  <br>
  <blockquote type="cite">
    <blockquote type="cite">
      <blockquote type="cite">Then the naming of the new methods in
DateTimeDataValue seemed wrong,
        <br>
I'm a great believer in ensuring such items and method names correctly
        <br>
reflect their purpose. This makes the code more readable.
        <br>
So we have
        <br>
        <br>
&nbsp;&nbsp;&nbsp;&nbsp; <br>
      </blockquote>
    </blockquote>
    <br>
&nbsp;
    <br>
    <br>
    <blockquote type="cite">
      <blockquote type="cite">It seems a more correct reflection of
their names would be intervalAdd
        <br>
and intervalDiff.
        <br>
        <br>
        <br>
&nbsp;&nbsp;&nbsp;&nbsp; <br>
      </blockquote>
The methods implement the TIMESTAMPADD and TIMESTAMPDIFF functions, so
      <br>
their names seem appropriate to me.
      <br>
&nbsp;&nbsp; <br>
    </blockquote>
    <br>
But they could be used to implement more in the future, and people
    <br>
looking at just that code may not understand the tie in to JDBC/ODBC
    <br>
functions. We don't control how api functions are named, but we can
    <br>
control how Derby functions are named to reflect their functionality.
    <br>
    <br>
&nbsp;
    <br>
    <br>
  </blockquote>
I think that the method names are good: the timestampAdd method
implements the TIMESTAMPADD escape function, the timestampDiff method
implements the TIMESTAMPDIFF escape function. The Javadoc comments all
say that they implement the JDBC escape function. If the methods are
modified in the future to do more then we can also rename them at that
time.
  <br>
  <br>
  <blockquote type="cite">
    <blockquote type="cite">I think that this is a reasonable argument,
but I think that we should
      <br>
follow the JDBC/ODBC standard, warts and all.
      <br>
&nbsp;&nbsp; <br>
    </blockquote>
    <br>
Yep, I keep forgetting that the JDBC spec is based upon the ODBC spec.
    <br>
    <br>
Dan.
    <br>
    <br>
    <br>
&nbsp;
    <br>
    <br>
  </blockquote>
  <br>
  <pre wrap="">
<hr size="4" width="90%">
Index: java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java	(revision 170691)
+++ java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java	(working copy)
@@ -468,6 +468,8 @@
           case C_NodeTypes.LOCATE_FUNCTION_NODE:
 		  case C_NodeTypes.SUBSTRING_OPERATOR_NODE:
 		  case C_NodeTypes.TRIM_OPERATOR_NODE:
+		  case C_NodeTypes.TIMESTAMP_ADD_FN_NODE:
+		  case C_NodeTypes.TIMESTAMP_DIFF_FN_NODE:
 		  	return C_NodeNames.TERNARY_OPERATOR_NODE_NAME;
 
 		  case C_NodeTypes.SELECT_NODE:
@@ -564,7 +566,7 @@
             return C_NodeNames.DB2_LENGTH_OPERATOR_NODE_NAME;
 
 		  // WARNING: WHEN ADDING NODE TYPES HERE, YOU MUST ALSO ADD
-		  // THEM TO $WS/tools/release/config/dbms/cloudscapenodes.properties
+		  // THEM TO tools/jar/DBMSnodes.properties
 
 		  default:
 			throw StandardException.newException(SQLState.NOT_IMPLEMENTED);
Index: java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java	(revision 170691)
+++ java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java	(working copy)
@@ -44,6 +44,7 @@
 
 import org.apache.derby.impl.sql.compile.ExpressionClassBuilder;
 import org.apache.derby.iapi.util.JBitSet;
+import org.apache.derby.iapi.util.ReuseFactory;
 
 import java.lang.reflect.Modifier;
 
@@ -78,17 +79,23 @@
 	public static final int LOCATE = 1;
 	public static final int SUBSTRING = 2;
 	public static final int LIKE = 3;
-	static final String[] TernaryOperators = {"trim", "LOCATE", "substring", "like"};
-	static final String[] TernaryMethodNames = {"trim", "locate", "substring", "like"};
+	public static final int TIMESTAMPADD = 4;
+	public static final int TIMESTAMPDIFF = 5;
+	static final String[] TernaryOperators = {"trim", "LOCATE", "substring", "like", "TIMESTAMPADD", "TIMESTAMPDIFF"};
+	static final String[] TernaryMethodNames = {"trim", "locate", "substring", "like", "timestampAdd", "timestampDiff"};
 	static final String[] TernaryResultType = {ClassName.StringDataValue, 
 			ClassName.NumberDataValue,
 			ClassName.ConcatableDataValue,
-			ClassName.BooleanDataValue};
+			ClassName.BooleanDataValue,
+            ClassName.DateTimeDataValue, 
+			ClassName.NumberDataValue};
 	static final String[][] TernaryArgType = {
 	{ClassName.StringDataValue, ClassName.StringDataValue, "java.lang.Integer"},
 	{ClassName.StringDataValue, ClassName.StringDataValue, ClassName.NumberDataValue},
 	{ClassName.ConcatableDataValue, ClassName.NumberDataValue, ClassName.NumberDataValue},
-	{ClassName.DataValueDescriptor, ClassName.DataValueDescriptor, ClassName.DataValueDescriptor}
+	{ClassName.DataValueDescriptor, ClassName.DataValueDescriptor, ClassName.DataValueDescriptor},
+    {ClassName.DateTimeDataValue, "java.lang.Integer", ClassName.NumberDataValue}, // time.timestampadd( interval, count)
+    {ClassName.DateTimeDataValue, "java.lang.Integer", ClassName.DateTimeDataValue}// time2.timestampDiff( interval, time1)
 	};
 
 	/**
@@ -233,6 +240,10 @@
 			locateBind();
 		else if (operatorType == SUBSTRING)
 			substrBind();
+		else if (operatorType == TIMESTAMPADD)
+            timestampAddBind();
+		else if (operatorType == TIMESTAMPDIFF)
+            timestampDiffBind();
 
 		return this;
 	}
@@ -330,6 +341,21 @@
 			nargs = 4;
 			receiverType = receiverInterfaceType;
 		}
+		else if (operatorType == TIMESTAMPADD || operatorType == TIMESTAMPDIFF)
+        {
+            Object intervalType = leftOperand.getConstantValueAsObject();
+            if( SanityManager.DEBUG)
+                SanityManager.ASSERT( intervalType != null &amp;&amp; intervalType instanceof Integer,
+                                      "Invalid interval type used for " + operator);
+            mb.push( ((Integer) intervalType).intValue());
+            rightOperand.generateExpression( acb, mb);
+            mb.upCast( TernaryArgType[ operatorType][2]);
+            acb.getCurrentDateExpression( mb);
+			mb.getField(field);
+			nargs = 4;
+			receiverType = receiverInterfaceType;
+        }
+            
 		mb.callMethod(VMOpcode.INVOKEINTERFACE, receiverType, methodName, resultInterfaceType, nargs);
 
 		/*
@@ -781,6 +807,70 @@
 		return this;
 	}
 
+
+	/**
+	 * Bind TIMESTAMPADD expression.  
+	 *
+	 * @return	The new top of the expression tree.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+
+ 	private ValueNode timestampAddBind() 
+			throws StandardException
+	{
+        if( ! bindParameter( rightOperand, Types.INTEGER))
+        {
+            if( ! rightOperand.getTypeId().isNumericTypeId())
+                throw StandardException.newException(SQLState.LANG_INVALID_FUNCTION_ARG_TYPE,
+                                                     rightOperand.getTypeId().getSQLTypeName(),
+                                                     ReuseFactory.getInteger( 2),
+                                                     operator);
+        }
+        bindDateTimeArg( receiver, 3);
+        setType(DataTypeDescriptor.getBuiltInDataTypeDescriptor( Types.TIMESTAMP));
+        return this;
+    } // end of timestampAddBind
+
+	/**
+	 * Bind TIMESTAMPDIFF expression.  
+	 *
+	 * @return	The new top of the expression tree.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+
+ 	private ValueNode timestampDiffBind() 
+			throws StandardException
+	{
+        bindDateTimeArg( rightOperand, 2);
+        bindDateTimeArg( receiver, 3);
+        setType(DataTypeDescriptor.getBuiltInDataTypeDescriptor( Types.INTEGER));
+        return this;
+    } // End of timestampDiffBind
+
+    private void bindDateTimeArg( ValueNode arg, int argNumber) throws StandardException
+    {
+        if( ! bindParameter( arg, Types.TIMESTAMP))
+        {
+            if( ! arg.getTypeId().isDateTimeTimeStampTypeId())
+                throw StandardException.newException(SQLState.LANG_INVALID_FUNCTION_ARG_TYPE,
+                                                     arg.getTypeId().getSQLTypeName(),
+                                                     ReuseFactory.getInteger( argNumber),
+                                                     operator);
+        }
+    } // end of bindDateTimeArg
+
+    private boolean bindParameter( ValueNode arg, int jdbcType) throws StandardException
+    {
+        if( arg.isParameterNode() &amp;&amp; arg.getTypeId() == null)
+        {
+            ((ParameterNode) arg).setDescriptor( new DataTypeDescriptor(TypeId.getBuiltInTypeId( jdbcType), true));
+            return true;
+        }
+        return false;
+    } // end of bindParameter
+
 	public ValueNode getReceiver()
 	{
 		return receiver;
Index: java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj	(revision 170691)
+++ java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj	(working copy)
@@ -1547,6 +1547,12 @@
 		throw StandardException.newException(SQLState.LANG_IDENTIFIER_TOO_LONG, identifier, String.valueOf(identifier_length_limit));
     }
 
+    private ValueNode getJdbcIntervalNode( int intervalType) throws StandardException
+    {
+        return (ValueNode) nodeFactory.getNode( C_NodeTypes.INT_CONSTANT_NODE,
+                                                ReuseFactory.getInteger( intervalType),
+                                                getContextManager());
+    }
 }
 
 PARSER_END(SQLParser)
@@ -1851,11 +1857,22 @@
 |	&lt;SAVEPOINT: "savepoint"&gt;
 |	&lt;SCALE: "scale"&gt;
 |	&lt;SERIALIZABLE: "serializable"&gt;
+|	&lt;SQL_TSI_FRAC_SECOND: "sql_tsi_frac_second"&gt;
+|	&lt;SQL_TSI_SECOND: "sql_tsi_second"&gt;
+|	&lt;SQL_TSI_MINUTE: "sql_tsi_minute"&gt;
+|	&lt;SQL_TSI_HOUR: "sql_tsi_hour"&gt;
+|	&lt;SQL_TSI_DAY: "sql_tsi_day"&gt;
+|	&lt;SQL_TSI_WEEK: "sql_tsi_week"&gt;
+|	&lt;SQL_TSI_MONTH: "sql_tsi_month"&gt;
+|	&lt;SQL_TSI_QUARTER: "sql_tsi_quarter"&gt;
+|	&lt;SQL_TSI_YEAR: "sql_tsi_year"&gt;
 |	&lt;START: "start"&gt;
 |	&lt;STATEMENT: "statement"&gt;
 |	&lt;THEN: "then"&gt;
 |	&lt;TIME: "time"&gt;
 |	&lt;TIMESTAMP: "timestamp"&gt;
+|	&lt;TIMESTAMPADD: "timestampadd"&gt;
+|	&lt;TIMESTAMPDIFF: "timestampdiff"&gt;
 |	&lt;TRUNCATE: "truncate"&gt;
 |	&lt;TYPE: "type"&gt;
 |	&lt;UNCOMMITTED: "uncommitted"&gt;
@@ -5473,10 +5490,106 @@
 	{
 		return value;
 	}
+|
+    value = timestampArithmeticFuncion()
+    {
+        return value;
+    }
+}
 
+/*
+ * &lt;A NAME="timestampArithmeticFuncion"&gt;timestampArithmeticFuncion&lt;/A&gt;
+ */
+ValueNode
+timestampArithmeticFuncion() throws StandardException :
+{
+    ValueNode intervalType;
+    ValueNode tstamp1;
+    ValueNode tstamp2;
+    ValueNode count;
 }
+{
+    &lt;TIMESTAMPADD&gt; &lt;LEFT_PAREN&gt; intervalType = jdbcIntervalType() &lt;COMMA&gt;
+       count = additiveExpression(null,0,false) &lt;COMMA&gt;
+       tstamp1 = additiveExpression(null,0,false) &lt;RIGHT_PAREN&gt;
+    {
+        return (ValueNode) nodeFactory.getNode( C_NodeTypes.TIMESTAMP_ADD_FN_NODE,
+                                                tstamp1,
+                                                intervalType,
+                                                count,
+                                                ReuseFactory.getInteger( TernaryOperatorNode.TIMESTAMPADD),
+                                                null,
+                                                getContextManager());
+    }
+|
+    &lt;TIMESTAMPDIFF&gt; &lt;LEFT_PAREN&gt; intervalType = jdbcIntervalType() &lt;COMMA&gt;
+       tstamp1 = additiveExpression(null,0,false) &lt;COMMA&gt;
+       tstamp2 = additiveExpression(null,0,false) &lt;RIGHT_PAREN&gt;
+    {
+        return (ValueNode) nodeFactory.getNode( C_NodeTypes.TIMESTAMP_DIFF_FN_NODE,
+                                                tstamp2,
+                                                intervalType,
+                                                tstamp1,
+                                                ReuseFactory.getInteger( TernaryOperatorNode.TIMESTAMPDIFF),
+                                                null,
+                                                getContextManager());
+    }
+}       
 
 /*
+ * &lt;A NAME="jdbcIntervalType"&gt;jdbcIntervalType&lt;/A&gt;
+ */
+ValueNode jdbcIntervalType() throws StandardException :
+{
+}
+{
+    &lt;SQL_TSI_FRAC_SECOND&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.FRAC_SECOND_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_SECOND&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.SECOND_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_MINUTE&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.MINUTE_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_HOUR&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.HOUR_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_DAY&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.DAY_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_WEEK&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.WEEK_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_MONTH&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.MONTH_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_QUARTER&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.QUARTER_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_YEAR&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.YEAR_INTERVAL);
+    }
+}
+
+/*
  * &lt;A NAME="numericValueFunction"&gt;numericValueFunction&lt;/A&gt;
  */
 ValueNode
@@ -11395,6 +11508,15 @@
 	|	tok = &lt;SHARE&gt;
 	|   tok = &lt;SPECIFIC&gt;
 	|	tok = &lt;SQLID&gt;
+	|	tok = &lt;SQL_TSI_FRAC_SECOND&gt;
+	|	tok = &lt;SQL_TSI_SECOND&gt;
+	|	tok = &lt;SQL_TSI_MINUTE&gt;
+	|	tok = &lt;SQL_TSI_HOUR&gt;
+	|	tok = &lt;SQL_TSI_DAY&gt;
+	|	tok = &lt;SQL_TSI_WEEK&gt;
+	|	tok = &lt;SQL_TSI_MONTH&gt;
+	|	tok = &lt;SQL_TSI_QUARTER&gt;
+	|	tok = &lt;SQL_TSI_YEAR&gt;
     |   tok = &lt;SQRT&gt;
     |       tok = &lt;STABILITY&gt;
 	|	tok = &lt;START&gt;
@@ -11404,6 +11526,8 @@
 	|	tok = &lt;THEN&gt;
 	|	tok = &lt;TIME&gt;
 	|	tok = &lt;TIMESTAMP&gt;
+	|	tok = &lt;TIMESTAMPADD&gt;
+	|	tok = &lt;TIMESTAMPDIFF&gt;
     |   tok = &lt;TRIGGER&gt;
 	|	tok = &lt;TRUNCATE&gt;
 	|	tok = &lt;TS&gt;
Index: java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
===================================================================
--- java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java	(revision 170691)
+++ java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java	(working copy)
@@ -186,7 +186,9 @@
 	// UNUSED static final int BOOLEAN_NODE = 155;
 	static final int DROP_ALIAS_NODE = 156;
     static final int INTERSECT_OR_EXCEPT_NODE = 157;
-	// 158 - 185 available
+	// 158 - 183 available
+    static final int TIMESTAMP_ADD_FN_NODE = 184;
+    static final int TIMESTAMP_DIFF_FN_NODE = 185;
 	static final int MODIFY_COLUMN_TYPE_NODE = 186;
 	static final int MODIFY_COLUMN_CONSTRAINT_NODE = 187;
     static final int ABSOLUTE_OPERATOR_NODE = 188;
Index: java/engine/org/apache/derby/iapi/types/DateTimeDataValue.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/DateTimeDataValue.java	(revision 170691)
+++ java/engine/org/apache/derby/iapi/types/DateTimeDataValue.java	(working copy)
@@ -31,6 +31,17 @@
 	public static final int MINUTE_FIELD = 4;
 	public static final int SECOND_FIELD = 5;
 
+    // The JDBC interval types
+    public static final int FRAC_SECOND_INTERVAL = 0;
+    public static final int SECOND_INTERVAL = 1;
+    public static final int MINUTE_INTERVAL = 2;
+    public static final int HOUR_INTERVAL = 3;
+    public static final int DAY_INTERVAL = 4;
+    public static final int WEEK_INTERVAL = 5;
+    public static final int MONTH_INTERVAL = 6;
+    public static final int QUARTER_INTERVAL = 7;
+    public static final int YEAR_INTERVAL = 8;
+
 	/**
 	 * Get the year number out of a date.
 	 *
@@ -108,5 +119,46 @@
 	 */
 	NumberDataValue getSeconds(NumberDataValue result)
 							throws StandardException;
+
+    /**
+     * Add a number of intervals to a datetime value. Implements the JDBC escape TIMESTAMPADD function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param intervalCount The number of intervals to add
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a DateTimeDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return startTime + intervalCount intervals, as a timestamp
+     *
+     * @exception StandardException
+     */
+    DateTimeDataValue timestampAdd( int intervalType,
+                                    NumberDataValue intervalCount,
+                                    java.sql.Date currentDate,
+                                    DateTimeDataValue resultHolder)
+        throws StandardException;
+
+    /**
+     * Finds the difference between two datetime values as a number of intervals. Implements the JDBC
+     * TIMESTAMPDIFF escape function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param time1
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a DateTimeDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return the number of intervals by which this datetime is greater than time1
+     *
+     * @exception StandardException
+     */
+    NumberDataValue timestampDiff( int intervalType,
+                                   DateTimeDataValue time1,
+                                   java.sql.Date currentDate,
+                                   NumberDataValue resultHolder)
+        throws StandardException;
 }
 
Index: java/engine/org/apache/derby/iapi/types/SQLDate.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/SQLDate.java	(revision 170691)
+++ java/engine/org/apache/derby/iapi/types/SQLDate.java	(working copy)
@@ -997,4 +997,56 @@
             throw se;
         }
     } // end of computeDateFunction
+
+    /**
+     * Add a number of intervals to a datetime value. Implements the JDBC escape TIMESTAMPADD function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param intervalCount The number of intervals to add
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a DateTimeDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return startTime + intervalCount intervals, as a timestamp
+     *
+     * @exception StandardException
+     */
+    public DateTimeDataValue timestampAdd( int intervalType,
+                                           NumberDataValue intervalCount,
+                                           java.sql.Date currentDate,
+                                           DateTimeDataValue resultHolder)
+        throws StandardException
+    {
+        return toTimestamp().timestampAdd( intervalType, intervalCount, currentDate, resultHolder);
+    }
+
+    private SQLTimestamp toTimestamp() throws StandardException
+    {
+        return new SQLTimestamp( getEncodedDate(), 0, 0);
+    }
+    
+    /**
+     * Finds the difference between two datetime values as a number of intervals. Implements the JDBC
+     * TIMESTAMPDIFF escape function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param time1
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a NumberDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return the number of intervals by which this datetime is greater than time1
+     *
+     * @exception StandardException
+     */
+    public NumberDataValue timestampDiff( int intervalType,
+                                          DateTimeDataValue time1,
+                                          java.sql.Date currentDate,
+                                          NumberDataValue resultHolder)
+        throws StandardException
+    {
+        return toTimestamp().timestampDiff( intervalType, time1, currentDate, resultHolder);
+    }
 }
Index: java/engine/org/apache/derby/iapi/types/SQLTime.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/SQLTime.java	(revision 170691)
+++ java/engine/org/apache/derby/iapi/types/SQLTime.java	(working copy)
@@ -965,5 +965,59 @@
 		currentCal.setTime(value);
 		return computeEncodedTime(currentCal);
 	}
+
+    /**
+     * Add a number of intervals to a datetime value. Implements the JDBC escape TIMESTAMPADD function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param intervalCount The number of intervals to add
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a DateTimeDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return startTime + intervalCount intervals, as a timestamp
+     *
+     * @exception StandardException
+     */
+    public DateTimeDataValue timestampAdd( int intervalType,
+                                           NumberDataValue intervalCount,
+                                           java.sql.Date currentDate,
+                                           DateTimeDataValue resultHolder)
+        throws StandardException
+    {
+        return toTimestamp( currentDate).timestampAdd( intervalType, intervalCount, currentDate, resultHolder);
+    }
+
+    private SQLTimestamp toTimestamp(java.sql.Date currentDate) throws StandardException
+    {
+        return new SQLTimestamp( SQLDate.computeEncodedDate( currentDate, (Calendar) null),
+                                 getEncodedTime(),
+                                 0 /* nanoseconds */);
+    }
+    
+    /**
+     * Finds the difference between two datetime values as a number of intervals. Implements the JDBC
+     * TIMESTAMPDIFF escape function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param time1
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a NumberDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return the number of intervals by which this datetime is greater than time1
+     *
+     * @exception StandardException
+     */
+    public NumberDataValue timestampDiff( int intervalType,
+                                          DateTimeDataValue time1,
+                                          java.sql.Date currentDate,
+                                          NumberDataValue resultHolder)
+        throws StandardException
+    {
+        return toTimestamp( currentDate ).timestampDiff( intervalType, time1, currentDate, resultHolder);
+    }
 }
 
Index: java/engine/org/apache/derby/iapi/types/SQLTimestamp.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/SQLTimestamp.java	(revision 170691)
+++ java/engine/org/apache/derby/iapi/types/SQLTimestamp.java	(working copy)
@@ -41,12 +41,11 @@
 import org.apache.derby.iapi.services.i18n.LocaleFinder;
 import org.apache.derby.iapi.services.cache.ClassSize;
 import org.apache.derby.iapi.util.StringUtil;
+import org.apache.derby.iapi.util.ReuseFactory;
 
 import org.apache.derby.iapi.types.SQLDouble;
 import org.apache.derby.iapi.types.SQLTime;
 
-
-
 import java.sql.Date;
 import java.sql.Time;
 import java.sql.Timestamp;
@@ -87,6 +86,8 @@
 
     static final int MAX_FRACTION_DIGITS = 6; // Only microsecond resolution on conversion to/from strings
     static final int FRACTION_TO_NANO = 1000; // 10**(9 - MAX_FRACTION_DIGITS)
+
+    static final int ONE_BILLION = 1000000000;
     
 	private int	encodedDate;
 	private int	encodedTime;
@@ -439,7 +440,7 @@
 		setValue(value, (Calendar) null);
 	}
 
-	private SQLTimestamp(int encodedDate, int encodedTime, int nanos) {
+	SQLTimestamp(int encodedDate, int encodedTime, int nanos) {
 
 		this.encodedDate = encodedDate;
 		this.encodedTime = encodedTime;
@@ -909,18 +910,24 @@
     {
         if( currentCal == null)
             currentCal = new GregorianCalendar();
-		currentCal.set(Calendar.YEAR, SQLDate.getYear(encodedDate));
-		/* Note calendar month is zero based so we subtract 1*/
-		currentCal.set(Calendar.MONTH, (SQLDate.getMonth(encodedDate)-1));
-		currentCal.set(Calendar.DATE, SQLDate.getDay(encodedDate));
-		currentCal.set(Calendar.HOUR_OF_DAY, SQLTime.getHour(encodedTime));
-		currentCal.set(Calendar.MINUTE, SQLTime.getMinute(encodedTime));
-		currentCal.set(Calendar.SECOND, SQLTime.getSecond(encodedTime));
-		currentCal.set(Calendar.MILLISECOND, 0);
+        setCalendar( currentCal);
 		Timestamp t = new Timestamp(currentCal.getTime().getTime());
 		t.setNanos(nanos);
 		return t;
 	}
+
+    private void setCalendar( Calendar cal)
+    {
+		cal.set(Calendar.YEAR, SQLDate.getYear(encodedDate));
+		/* Note calendar month is zero based so we subtract 1*/
+		cal.set(Calendar.MONTH, (SQLDate.getMonth(encodedDate)-1));
+		cal.set(Calendar.DATE, SQLDate.getDay(encodedDate));
+		cal.set(Calendar.HOUR_OF_DAY, SQLTime.getHour(encodedTime));
+		cal.set(Calendar.MINUTE, SQLTime.getMinute(encodedTime));
+		cal.set(Calendar.SECOND, SQLTime.getSecond(encodedTime));
+		cal.set(Calendar.MILLISECOND, 0);
+    } // end of setCalendar
+        
 	/**
 	 * Set the encoded values for the timestamp
 	 *
@@ -1052,4 +1059,308 @@
         }
         return retVal;
     } // end of parseDateTimeInteger
+
+    /**
+     * Add a number of intervals to a datetime value. Implements the JDBC escape TIMESTAMPADD function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param intervalCount The number of intervals to add
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a DateTimeDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return startTime + intervalCount intervals, as a timestamp
+     *
+     * @exception StandardException
+     */
+    public DateTimeDataValue timestampAdd( int intervalType,
+                                           NumberDataValue count,
+                                           java.sql.Date currentDate,
+                                           DateTimeDataValue resultHolder)
+        throws StandardException
+    {
+        if( resultHolder == null)
+            resultHolder = new SQLTimestamp();
+        SQLTimestamp tsResult = (SQLTimestamp) resultHolder;
+        if( isNull() || count.isNull())
+        {
+            tsResult.restoreToNull();
+            return resultHolder;
+        }
+        tsResult.setFrom( this);
+        int intervalCount = count.getInt();
+        
+        switch( intervalType)
+        {
+        case FRAC_SECOND_INTERVAL:
+            // The interval is nanoseconds. Do the computation in long to avoid overflow.
+            long nanos = this.nanos + intervalCount;
+            if( nanos &gt;= 0 &amp;&amp; nanos &lt; ONE_BILLION)
+                tsResult.nanos = (int) nanos;
+            else
+            {
+                int secondsInc = (int)(nanos/ONE_BILLION);
+                if( nanos &gt;= 0)
+                    tsResult.nanos = (int) (nanos % ONE_BILLION);
+                else
+                {
+                    secondsInc--;
+                    nanos -= secondsInc * (long)ONE_BILLION; // 0 &lt;= nanos &lt; ONE_BILLION
+                    tsResult.nanos = (int) nanos;
+                }
+                addInternal( Calendar.SECOND, secondsInc, tsResult);
+            }
+            break;
+
+        case SECOND_INTERVAL:
+            addInternal( Calendar.SECOND, intervalCount, tsResult);
+            break;
+
+        case MINUTE_INTERVAL:
+            addInternal( Calendar.MINUTE, intervalCount, tsResult);
+            break;
+
+        case HOUR_INTERVAL:
+            addInternal( Calendar.HOUR, intervalCount, tsResult);
+            break;
+
+        case DAY_INTERVAL:
+            addInternal( Calendar.DATE, intervalCount, tsResult);
+            break;
+
+        case WEEK_INTERVAL:
+            addInternal( Calendar.DATE, intervalCount*7, tsResult);
+            break;
+
+        case MONTH_INTERVAL:
+            addInternal( Calendar.MONTH, intervalCount, tsResult);
+            break;
+
+        case QUARTER_INTERVAL:
+            addInternal( Calendar.MONTH, intervalCount*3, tsResult);
+            break;
+
+        case YEAR_INTERVAL:
+            addInternal( Calendar.YEAR, intervalCount, tsResult);
+            break;
+
+        default:
+            throw StandardException.newException( SQLState.LANG_INVALID_FUNCTION_ARGUMENT,
+                                                  ReuseFactory.getInteger( intervalType),
+                                                  "TIMESTAMPADD");
+        }
+        return tsResult;
+    } // end of timestampAdd
+
+    private void addInternal( int calIntervalType, int count, SQLTimestamp tsResult) throws StandardException
+    {
+        Calendar cal = new GregorianCalendar();
+        setCalendar( cal);
+        try
+        {
+            cal.add( calIntervalType, count);
+            tsResult.encodedTime = SQLTime.computeEncodedTime( cal);
+            tsResult.encodedDate = SQLDate.computeEncodedDate( cal);
+        }
+        catch( StandardException se)
+        {
+            String state = se.getSQLState();
+            if( state != null &amp;&amp; state.length() &gt; 0 &amp;&amp; SQLState.LANG_DATE_RANGE_EXCEPTION.startsWith( state))
+            {
+                throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE, "TIMESTAMP");
+            }
+            throw se;
+        }
+    } // end of addInternal
+
+    /**
+     * Finds the difference between two datetime values as a number of intervals. Implements the JDBC
+     * TIMESTAMPDIFF escape function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param time1
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a NumberDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return the number of intervals by which this datetime is greater than time1
+     *
+     * @exception StandardException
+     */
+    public NumberDataValue timestampDiff( int intervalType,
+                                          DateTimeDataValue time1,
+                                          java.sql.Date currentDate,
+                                          NumberDataValue resultHolder)
+        throws StandardException
+    {
+        if( resultHolder == null)
+            resultHolder = new SQLInteger();
+ 
+       if( isNull() || time1.isNull())
+        {
+            resultHolder.setToNull();
+            return resultHolder;
+        }
+        
+        SQLTimestamp ts1 = promote( time1, currentDate);
+
+        /* Years, months, and quarters are difficult because their lengths are not constant.
+         * The other intervals are relatively easy (because we ignore leap seconds).
+         */
+        Calendar cal = new GregorianCalendar();
+        setCalendar( cal);
+        long thisInSeconds = cal.getTime().getTime()/1000;
+        ts1.setCalendar( cal);
+        long ts1InSeconds = cal.getTime().getTime()/1000;
+        long secondsDiff = thisInSeconds - ts1InSeconds;
+        int nanosDiff = nanos - ts1.nanos;
+        // Normalize secondsDiff and nanosDiff so that they are both &lt;= 0 or both &gt;= 0.
+        if( nanosDiff &lt; 0 &amp;&amp; secondsDiff &gt; 0)
+        {
+            secondsDiff--;
+            nanosDiff += ONE_BILLION;
+        }
+        else if( nanosDiff &gt; 0 &amp;&amp; secondsDiff &lt; 0)
+        {
+            secondsDiff++;
+            nanosDiff -= ONE_BILLION;
+        }
+        long ldiff = 0;
+        
+        switch( intervalType)
+        {
+        case FRAC_SECOND_INTERVAL:
+            if( secondsDiff &gt; Integer.MAX_VALUE/ONE_BILLION || secondsDiff &lt; Integer.MIN_VALUE/ONE_BILLION)
+                throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE, "INTEGER");
+            ldiff = secondsDiff*ONE_BILLION + nanosDiff;
+            break;
+            
+        case SECOND_INTERVAL:
+            ldiff = secondsDiff;
+            break;
+            
+        case MINUTE_INTERVAL:
+            ldiff = secondsDiff/60;
+            break;
+
+        case HOUR_INTERVAL:
+            ldiff = secondsDiff/(60*60);
+            break;
+            
+        case DAY_INTERVAL:
+            ldiff = secondsDiff/(24*60*60);
+            break;
+            
+        case WEEK_INTERVAL:
+            ldiff = secondsDiff/(7*24*60*60);
+            break;
+
+        case QUARTER_INTERVAL:
+        case MONTH_INTERVAL:
+            // Make a conservative guess and increment until we overshoot.
+            if( Math.abs( secondsDiff) &gt; 366*24*60*60) // Certainly more than a year
+                ldiff = 12*(secondsDiff/(366*24*60*60));
+            else
+                ldiff = secondsDiff/(31*24*60*60);
+            if( secondsDiff &gt;= 0)
+            {
+                if (ldiff &gt;= Integer.MAX_VALUE)
+                    throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE, "INTEGER");
+                // cal holds the time for time1
+                cal.add( Calendar.MONTH, (int) (ldiff + 1));
+                for(;;)
+                {
+                    if( cal.getTime().getTime()/1000 &gt; thisInSeconds)
+                        break;
+                    cal.add( Calendar.MONTH, 1);
+                    ldiff++;
+                }
+            }
+            else
+            {
+                if (ldiff &lt;= Integer.MIN_VALUE)
+                    throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE, "INTEGER");
+                // cal holds the time for time1
+                cal.add( Calendar.MONTH, (int) (ldiff - 1));
+                for(;;)
+                {
+                    if( cal.getTime().getTime()/1000 &lt; thisInSeconds)
+                        break;
+                    cal.add( Calendar.MONTH, -1);
+                    ldiff--;
+                }
+            }
+            if( intervalType == QUARTER_INTERVAL)
+                ldiff = ldiff/3;
+            break;
+
+        case YEAR_INTERVAL:
+            // Make a conservative guess and increment until we overshoot.
+            ldiff = secondsDiff/(366*24*60*60);
+            if( secondsDiff &gt;= 0)
+            {
+                if (ldiff &gt;= Integer.MAX_VALUE)
+                    throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE, "INTEGER");
+                // cal holds the time for time1
+                cal.add( Calendar.YEAR, (int) (ldiff + 1));
+                for(;;)
+                {
+                    if( cal.getTime().getTime()/1000 &gt; thisInSeconds)
+                        break;
+                    cal.add( Calendar.YEAR, 1);
+                    ldiff++;
+                }
+            }
+            else
+            {
+                if (ldiff &lt;= Integer.MIN_VALUE)
+                    throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE, "INTEGER");
+                // cal holds the time for time1
+                cal.add( Calendar.YEAR, (int) (ldiff - 1));
+                for(;;)
+                {
+                    if( cal.getTime().getTime()/1000 &lt; thisInSeconds)
+                        break;
+                    cal.add( Calendar.YEAR, -1);
+                    ldiff--;
+                }
+            }
+            break;
+
+        default:
+            throw StandardException.newException( SQLState.LANG_INVALID_FUNCTION_ARGUMENT,
+                                                  ReuseFactory.getInteger( intervalType),
+                                                  "TIMESTAMPDIFF");
+        }
+		if (ldiff &gt; Integer.MAX_VALUE || ldiff &lt; Integer.MIN_VALUE)
+			throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE, "INTEGER");
+        resultHolder.setValue( (int) ldiff);
+        return resultHolder;
+    } // end of timestampDiff
+
+    /**
+     * Promotes a DateTimeDataValue to a timestamp.
+     *
+     * @param datetime
+     *
+     * @return the corresponding timestamp, using the current date if datetime is a time,
+     *         or time 00:00:00 if datetime is a date.
+     *
+     * @exception StandardException
+     */
+    static SQLTimestamp promote( DateTimeDataValue dateTime, java.sql.Date currentDate) throws StandardException
+    {
+        if( dateTime instanceof SQLTimestamp)
+            return (SQLTimestamp) dateTime;
+        else if( dateTime instanceof SQLTime)
+            return new SQLTimestamp( SQLDate.computeEncodedDate( currentDate, (Calendar) null),
+                                    ((SQLTime) dateTime).getEncodedTime(),
+                                    0 /* nanoseconds */);
+        else if( dateTime instanceof SQLDate)
+            return new SQLTimestamp( ((SQLDate) dateTime).getEncodedDate(), 0, 0);
+        else
+            return new SQLTimestamp( dateTime.getTimestamp( new GregorianCalendar()));
+    } // end of promote
 }
Index: java/engine/org/apache/derby/iapi/reference/SQLState.java
===================================================================
--- java/engine/org/apache/derby/iapi/reference/SQLState.java	(revision 170691)
+++ java/engine/org/apache/derby/iapi/reference/SQLState.java	(working copy)
@@ -752,7 +752,7 @@
 	String LANG_NOT_STORABLE                                           = "42821";
 	String LANG_NULL_RESULT_SET_META_DATA                              = "42X43";
 	String LANG_INVALID_COLUMN_LENGTH                                  = "42X44";
-	// = "42X45";
+	String LANG_INVALID_FUNCTION_ARG_TYPE                              = "42X45";
 	// = "42X46";
 	// = "42X47";
 	String LANG_INVALID_PRECISION                                      = "42X48";
Index: java/engine/org/apache/derby/loc/messages_en.properties
===================================================================
--- java/engine/org/apache/derby/loc/messages_en.properties	(revision 170691)
+++ java/engine/org/apache/derby/loc/messages_en.properties	(working copy)
@@ -458,7 +458,7 @@
 42821=Columns of type ''{0}'' cannot hold values of type ''{1}''. 
 42X43=The ResultSetMetaData returned for the class/object ''{0}'' was null. The ResultSetMetaData must be non-null in order to use this class as an external virtual table.
 42X44=Invalid length ''{0}'' in column specification.
-# 42X45=
+42X45={0} is an invalid type for argument number {1} of {2}.
 # 42X46=
 # 42X47=
 42X48=Value ''{1}'' is not a valid precision for {0}.
Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/timestampArith.java
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/timestampArith.java	(revision 0)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/timestampArith.java	(revision 0)
@@ -0,0 +1,787 @@
+/*
+
+   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.timestampArith
+
+   Copyright 2005 The Apache Software Foundation or its licensors, as applicable.
+
+   Licensed under the Apache License, Version 2.0 (the "License");
+   you may not use this file except in compliance with the License.
+   You may obtain a copy of the License at
+
+      <a class="moz-txt-link-freetext" href="http://www.apache.org/licenses/LICENSE-2.0">http://www.apache.org/licenses/LICENSE-2.0</a>
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import org.apache.derby.tools.ij;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.Statement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Timestamp;
+import java.sql.Types;
+
+import java.util.Calendar;
+
+/**
+ * Test the JDBC TIMESTAMPADD and TIMESTAMPDIFF escape functions.
+ *
+ * Things to test:
+ *   + Test each interval type with timestamp, date, and time inputs.
+ *   + Test diff with all 9 combinations of datetime input types (timestamp - timestamp, timestamp - date, etc).
+ *   + Test PreparedStatements with parameters, '?', in each argument, and Statements. (Statements are prepared
+ *     internally so we do not also have to test PrepardStatements without parameters).
+ *   + Test with null inputs.
+ *   + Test with input string that is convertible to timestamp.
+ *   + Test with invalid interval type.
+ *   + Test with invalid arguments in the date time arguments.
+ *   + Test TIMESTAMPADD with an invalid type in the count argument.
+ *   + Test overflow cases.
+ */
+public class timestampArith
+{
+    private static final int FRAC_SECOND_INTERVAL = 0;
+    private static final int SECOND_INTERVAL = 1;
+    private static final int MINUTE_INTERVAL = 2;
+    private static final int HOUR_INTERVAL = 3;
+    private static final int DAY_INTERVAL = 4;
+    private static final int WEEK_INTERVAL = 5;
+    private static final int MONTH_INTERVAL = 6;
+    private static final int QUARTER_INTERVAL = 7;
+    private static final int YEAR_INTERVAL = 8;
+    private static final String[] intervalJdbcNames =
+    {"SQL_TSI_FRAC_SECOND", "SQL_TSI_SECOND", "SQL_TSI_MINUTE", "SQL_TSI_HOUR",
+     "SQL_TSI_DAY", "SQL_TSI_WEEK", "SQL_TSI_MONTH", "SQL_TSI_QUARTER", "SQL_TSI_YEAR"};
+
+    private static final int ONE_BILLION = 1000000000;
+
+    int errorCount = 0;
+    private Connection conn;
+    private PreparedStatement[] tsAddPS = new PreparedStatement[intervalJdbcNames.length];
+    private PreparedStatement[] tsDiffPS = new PreparedStatement[intervalJdbcNames.length];
+    private Statement stmt;
+    private static final String TODAY;
+    private static final String TOMORROW;
+    private static final String YEAR_FROM_TOMORROW;
+    private static final String YEAR_FROM_TODAY;
+    private static final String YESTERDAY;
+    private static final String WEEK_FROM_TODAY;
+    static {
+        Calendar cal = Calendar.getInstance();
+        // Make sure that we are not so close to midnight that TODAY might be yesterday before
+        // we are finished using it.
+        while( cal.get( Calendar.HOUR) == 23 &amp;&amp; cal.get( Calendar.MINUTE) == 58)
+        {
+            try
+            {
+                Thread.sleep( (60 - cal.get( Calendar.SECOND))*1000);
+            }
+            catch( InterruptedException ie) {};
+            cal = Calendar.getInstance();
+        }
+        TODAY = isoFormatDate( cal);
+        cal.add( Calendar.DATE, -1);
+        YESTERDAY = isoFormatDate( cal);
+        cal.add( Calendar.DATE, 2);
+        TOMORROW = isoFormatDate( cal);
+        cal.add( Calendar.YEAR, 1);
+        YEAR_FROM_TOMORROW = isoFormatDate( cal);
+        cal.add( Calendar.DATE, -1);
+        YEAR_FROM_TODAY = isoFormatDate( cal);
+        cal.add( Calendar.YEAR, -1); // today
+        cal.add( Calendar.DATE, 7);
+        WEEK_FROM_TODAY = isoFormatDate( cal);
+    }
+    
+    private static String isoFormatDate( Calendar cal)
+    {
+        StringBuffer sb = new StringBuffer();
+        String s = String.valueOf( cal.get( Calendar.YEAR));
+        for( int i = s.length(); i &lt; 4; i++)
+            sb.append( '0');
+        sb.append( s);
+        sb.append( '-');
+
+        s = String.valueOf( cal.get( Calendar.MONTH) + 1);
+        for( int i = s.length(); i &lt; 2; i++)
+            sb.append( '0');
+        sb.append( s);
+        sb.append( '-');
+
+        s = String.valueOf( cal.get( Calendar.DAY_OF_MONTH));
+        for( int i = s.length(); i &lt; 2; i++)
+            sb.append( '0');
+        sb.append( s);
+
+        return sb.toString();
+    }
+    
+    private final OneTest[] tests =
+    {
+        // timestamp - timestamp
+        new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2005-05-10 08:25:00"), ts("2005-05-10 08:25:00.000001"), 1000,
+                         null, null),
+        new OneDiffTest( SECOND_INTERVAL, ts( "2005-05-10 08:25:01"), ts("2005-05-10 08:25:00"), -1, null, null),
+        new OneDiffTest( SECOND_INTERVAL, ts( "2005-05-10 08:25:00.1"), ts("2005-05-10 08:25:00"), 0, null, null),
+        new OneDiffTest( SECOND_INTERVAL, ts( "2005-05-10 08:25:00"), ts("2005-05-10 08:26:00"), 60, null, null),
+        new OneDiffTest( MINUTE_INTERVAL, ts( "2005-05-11 08:25:00"), ts("2005-05-10 08:25:00"), -24*60, null, null),
+        new OneDiffTest( HOUR_INTERVAL, ts("2005-05-10 08:25:00"), ts( "2005-05-11 08:25:00"), 24, null, null),
+        new OneDiffTest( DAY_INTERVAL, ts("2005-05-10 08:25:00"), ts( "2005-05-11 08:25:00"), 1, null, null),
+        new OneDiffTest( DAY_INTERVAL, ts("2005-05-10 08:25:01"), ts( "2005-05-11 08:25:00"), 0, null, null),
+        new OneDiffTest( WEEK_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-03-01 08:25:00"), 0, null, null),
+        new OneDiffTest( MONTH_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-03-23 08:25:00"), 1, null, null),
+        new OneDiffTest( MONTH_INTERVAL, ts("2005-02-23 08:25:01"), ts( "2005-03-23 08:25:00"), 0, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-05-23 08:25:00"), 1, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, ts("2005-02-23 08:25:01"), ts( "2005-05-23 08:25:00"), 0, null, null),
+        new OneDiffTest( YEAR_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-05-23 08:25:00"), 0, null, null),
+        new OneDiffTest( YEAR_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2006-02-23 08:25:00"), 1, null, null),
+
+        // timestamp - time, time - timestamp
+        new OneDiffTest( FRAC_SECOND_INTERVAL, ts( TODAY + " 10:00:00.123456"), tm( "10:00:00"), -123456000, null, null),
+        new OneDiffTest( FRAC_SECOND_INTERVAL, tm( "10:00:00"), ts( TODAY + " 10:00:00.123456"), 123456000, null, null),
+        new OneDiffTest( SECOND_INTERVAL, ts( TODAY + " 10:00:00.1"), tm( "10:00:01"), 0, null, null),
+        new OneDiffTest( SECOND_INTERVAL, tm( "10:00:01"), ts( TODAY + " 10:00:00"), -1, null, null),
+        new OneDiffTest( MINUTE_INTERVAL, ts( TODAY + " 10:02:00"), tm( "10:00:00"), -2, null, null),
+        new OneDiffTest( MINUTE_INTERVAL, tm( "11:00:00"), ts( TODAY + " 10:02:00"), -58, null, null),
+        new OneDiffTest( HOUR_INTERVAL, ts( TODAY + " 10:02:00"), tm( "10:00:00"), 0, null, null),
+        new OneDiffTest( HOUR_INTERVAL, tm( "10:00:00"), ts( TODAY + " 23:02:00"), 13, null, null),
+        new OneDiffTest( DAY_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
+        new OneDiffTest( DAY_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
+        new OneDiffTest( WEEK_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
+        new OneDiffTest( WEEK_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
+        new OneDiffTest( MONTH_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
+        new OneDiffTest( MONTH_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
+        new OneDiffTest( YEAR_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
+        new OneDiffTest( YEAR_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
+
+        // timestamp - date, date - timestamp
+        new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2004-05-10 00:00:00.123456"), dt("2004-05-10"), -123456000,
+                         null, null),
+        new OneDiffTest( FRAC_SECOND_INTERVAL, dt("2004-05-10"), ts( "2004-05-10 00:00:00.123456"), 123456000,
+                         null, null),
+        new OneDiffTest( SECOND_INTERVAL, ts( "2004-05-10 08:25:01"), dt("2004-05-10"), -(1+60*(25+60*8)), null, null),
+        new OneDiffTest( SECOND_INTERVAL, dt( "2004-05-10"), ts("2004-05-09 23:59:00"), -60, null, null),
+        new OneDiffTest( MINUTE_INTERVAL, ts( "2004-05-11 08:25:00"), dt("2004-05-10"), -(24*60+8*60+25), null, null),
+        new OneDiffTest( MINUTE_INTERVAL, dt("2004-05-10"), ts( "2004-05-11 08:25:00"), 24*60+8*60+25, null, null),
+        new OneDiffTest( HOUR_INTERVAL, ts("2004-02-28 08:25:00"), dt( "2004-03-01"), 39, null, null),
+        new OneDiffTest( HOUR_INTERVAL, dt( "2005-03-01"), ts("2005-02-28 08:25:00"), -15, null, null),
+        new OneDiffTest( DAY_INTERVAL, ts("2004-05-10 08:25:00"), dt( "2004-05-11"), 0, null, null),
+        new OneDiffTest( DAY_INTERVAL, dt("2004-05-10"), ts( "2004-05-11 08:25:00"), 1, null, null),
+        new OneDiffTest( WEEK_INTERVAL, ts("2004-02-23 00:00:00"), dt( "2004-03-01"), 1, null, null),
+        new OneDiffTest( WEEK_INTERVAL, dt( "2004-03-01"), ts("2004-02-23 00:00:00"), -1, null, null),
+        new OneDiffTest( MONTH_INTERVAL, ts("2004-02-23 08:25:00"), dt( "2004-03-24"), 1, null, null),
+        new OneDiffTest( MONTH_INTERVAL, dt( "2005-03-24"), ts("2004-02-23 08:25:00"), -13, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, ts("2004-02-23 08:25:00"), dt( "2004-05-24"), 1, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, dt( "2004-05-23"), ts("2004-02-23 08:25:01"), 0, null, null),
+        new OneDiffTest( YEAR_INTERVAL, ts("2004-02-23 08:25:00"), dt( "2004-05-23"), 0, null, null),
+        new OneDiffTest( YEAR_INTERVAL, dt( "2004-05-23"), ts("2003-02-23 08:25:00"), -1, null, null),
+
+        // date - time, time - date
+        new OneDiffTest( FRAC_SECOND_INTERVAL, dt( TODAY), tm("00:00:01"), ONE_BILLION, null, null),
+        new OneDiffTest( FRAC_SECOND_INTERVAL, tm("00:00:02"), dt( TODAY), -2*ONE_BILLION, null, null),
+        new OneDiffTest( SECOND_INTERVAL, dt( TODAY), tm("00:00:01"), 1, null, null),
+        new OneDiffTest( SECOND_INTERVAL, tm("00:00:02"), dt( TODAY), -2, null, null),
+        new OneDiffTest( MINUTE_INTERVAL, dt( TODAY), tm("12:34:56"), 12*60 + 34, null, null),
+        new OneDiffTest( MINUTE_INTERVAL, tm("12:34:56"), dt( TODAY), -(12*60 + 34), null, null),
+        new OneDiffTest( HOUR_INTERVAL, dt( TODAY), tm("12:34:56"), 12, null, null),
+        new OneDiffTest( HOUR_INTERVAL, tm("12:34:56"), dt( TODAY), -12, null, null),
+        new OneDiffTest( DAY_INTERVAL, dt( TOMORROW), tm( "00:00:00"), -1, null, null),
+        new OneDiffTest( DAY_INTERVAL, tm( "00:00:00"), dt( TOMORROW), 1, null, null),
+        new OneDiffTest( WEEK_INTERVAL, dt( TOMORROW), tm( "00:00:00"), 0, null, null),
+        new OneDiffTest( WEEK_INTERVAL, tm( "00:00:00"), dt( TOMORROW), 0, null, null),
+        new OneDiffTest( MONTH_INTERVAL, dt( YEAR_FROM_TOMORROW), tm( "12:00:00"), -12, null, null),
+        new OneDiffTest( MONTH_INTERVAL, tm( "12:00:00"), dt( YEAR_FROM_TOMORROW), 12, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, dt( YEAR_FROM_TOMORROW), tm( "12:00:00"), -4, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, tm( "12:00:00"), dt( YEAR_FROM_TOMORROW), 4, null, null),
+        new OneDiffTest( YEAR_INTERVAL, dt( YEAR_FROM_TOMORROW), tm( "12:00:00"), -1, null, null),
+        new OneDiffTest( YEAR_INTERVAL, tm( "12:00:00"), dt( YEAR_FROM_TOMORROW), 1, null, null),
+
+        // Test add with all combinatons of interval types and datetime types
+        new OneAddTest( FRAC_SECOND_INTERVAL, 1000, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:55:00.000001"),
+                        null, null),
+        new OneAddTest( FRAC_SECOND_INTERVAL, -1000, dt("2005-05-11"), ts("2005-05-10 23:59:59.999999"),
+                        null, null),
+        new OneAddTest( FRAC_SECOND_INTERVAL, ONE_BILLION, tm("23:59:59"), ts( TOMORROW + " 00:00:00"), null, null),
+        new OneAddTest( SECOND_INTERVAL, 60, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:56:00"), null, null),
+        new OneAddTest( SECOND_INTERVAL, 60, dt("2005-05-11"), ts("2005-05-11 00:01:00"), null, null),
+        new OneAddTest( SECOND_INTERVAL, 60, tm("23:59:30"), ts( TOMORROW + " 00:00:30"), null, null),
+        new OneAddTest( MINUTE_INTERVAL, -1, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:54:00"), null, null),
+        new OneAddTest( MINUTE_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-11 00:01:00"), null, null),
+        new OneAddTest( MINUTE_INTERVAL, 1, tm("12:00:00"), ts( TODAY + " 12:01:00"), null, null),
+        new OneAddTest( HOUR_INTERVAL, 2, ts("2005-05-11 15:55:00"), ts("2005-05-11 17:55:00"), null, null),
+        new OneAddTest( HOUR_INTERVAL, -2, dt("2005-05-11"), ts("2005-05-10 22:00:00"), null, null),
+        new OneAddTest( HOUR_INTERVAL, 1, tm("12:00:00"), ts( TODAY + " 13:00:00"), null, null),
+        new OneAddTest( DAY_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-05-12 15:55:00"), null, null),
+        new OneAddTest( DAY_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-12 00:00:00"), null, null),
+        new OneAddTest( DAY_INTERVAL, -1, tm( "12:00:00"), ts( YESTERDAY + " 12:00:00"), null, null),
+        new OneAddTest( WEEK_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-05-18 15:55:00"), null, null),
+        new OneAddTest( WEEK_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-18 00:00:00"), null, null),
+        new OneAddTest( WEEK_INTERVAL, 1, tm("12:00:00"), ts( WEEK_FROM_TODAY + " 12:00:00"), null, null),
+        new OneAddTest( MONTH_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-06-11 15:55:00"), null, null),
+        new OneAddTest( MONTH_INTERVAL, -1, dt("2005-03-29"), ts("2005-02-28 00:00:00"), null, null),
+        new OneAddTest( MONTH_INTERVAL, 12, tm( "12:00:00"), ts( YEAR_FROM_TODAY + " 12:00:00"), null, null),
+        new OneAddTest( QUARTER_INTERVAL, 1, ts("2005-10-11 15:55:00"), ts("2006-01-11 15:55:00"), null, null),
+        new OneAddTest( QUARTER_INTERVAL, -2, dt( "2005-05-05"), ts( "2004-11-05 00:00:00"), null, null),
+        new OneAddTest( QUARTER_INTERVAL, 4, tm( "12:00:00"), ts( YEAR_FROM_TODAY + " 12:00:00"), null, null),
+        new OneAddTest( YEAR_INTERVAL, -10, ts("2005-10-11 15:55:00"), ts("1995-10-11 15:55:00"), null, null),
+        new OneAddTest( YEAR_INTERVAL, 2, dt( "2005-05-05"), ts( "2007-05-05 00:00:00"), null, null),
+        new OneAddTest( YEAR_INTERVAL, 1, tm( "12:00:00"), ts( YEAR_FROM_TODAY + " 12:00:00"), null, null),
+
+        // String inputs
+        new OneStringDiffTest( SECOND_INTERVAL, "2005-05-10 08:25:00", "2005-05-10 08:26:00", 60, null, null),
+        new OneStringAddTest( DAY_INTERVAL, 1, "2005-05-11 15:55:00", ts("2005-05-12 15:55:00"), null, null),
+
+        // Overflow
+        new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2004-05-10 00:00:00.123456"), ts( "2004-05-10 00:00:10.123456"), 0,
+                         "22003", "The resulting value is outside the range for the data type INTEGER."),
+        new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2004-05-10 00:00:00.123456"), ts( "2005-05-10 00:00:00.123456"), 0,
+                         "22003", "The resulting value is outside the range for the data type INTEGER."),
+        new OneDiffTest( SECOND_INTERVAL, ts( "1904-05-10 00:00:00"), ts( "2205-05-10 00:00:00"), 0,
+                         "22003", "The resulting value is outside the range for the data type INTEGER."),
+        new OneAddTest( YEAR_INTERVAL, 99999, ts( "2004-05-10 00:00:00.123456"), null,
+                        "22003", "The resulting value is outside the range for the data type TIMESTAMP.")
+    };
+
+    private final String[][] invalid =
+    {
+        {"values( {fn TIMESTAMPDIFF( SECOND, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)})", "42X01",
+         "Syntax error: Encountered \"SECOND\" at line 1, column 28."},
+        {"values( {fn TIMESTAMPDIFF( , CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)})", "42X01",
+         "Syntax error: Encountered \",\" at line 1, column 28."},
+        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 5)})", "42X01",
+         "Syntax error: Encountered \",\" at line 1, column 80."},
+        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP, 'x')})", "42X45",
+         "CHAR is an invalid type for argument number 3 of TIMESTAMPDIFF."},
+        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, 'x', CURRENT_TIMESTAMP)})", "42X45",
+         "CHAR is an invalid type for argument number 2 of TIMESTAMPDIFF."},
+        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP)})", "42X01",
+         "Syntax error: Encountered \")\" at line 1, column 61."},
+        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND)})", "42X01",
+         "Syntax error: Encountered \")\" at line 1, column 42."},
+        {"values( {fn TIMESTAMPADD( x, 1, CURRENT_TIMESTAMP)})", "42X01",
+           "Syntax error: Encountered \"x\" at line 1, column 27."},
+        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, CURRENT_DATE, CURRENT_TIMESTAMP)})", "42X45",
+           "DATE is an invalid type for argument number 2 of TIMESTAMPADD."},
+        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 'XX', CURRENT_TIMESTAMP)})", "42X45",
+           "CHAR is an invalid type for argument number 2 of TIMESTAMPADD."},
+        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1, 2.1)})", "42X45",
+           "DECIMAL is an invalid type for argument number 3 of TIMESTAMPADD."},
+        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1, 'XX')})", "42X45",
+           "CHAR is an invalid type for argument number 3 of TIMESTAMPADD."},
+        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1)})", "42X01",
+           "Syntax error: Encountered \")\" at line 1, column 44."},
+        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND)})", "42X01",
+           "Syntax error: Encountered \")\" at line 1, column 41."}
+    };
+
+    private static java.sql.Timestamp ts( String s)
+    {
+        // Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff
+        if( s.length() &lt; 29)
+        {
+            // Pad out the fraction with zeros
+            StringBuffer sb = new StringBuffer( s);
+            if( s.length() == 19)
+                sb.append( '.');
+            while( sb.length() &lt; 29)
+                sb.append( '0');
+            s = sb.toString();
+        }
+        try
+        {
+            return java.sql.Timestamp.valueOf( s);
+        }
+        catch( Exception e)
+        {
+            System.out.println( s + " is not a proper timestamp string.");
+            System.out.println( e.getClass().getName() + ": " + e.getMessage());
+            e.printStackTrace();
+            System.exit(1);
+            return null;
+        }
+    }
+
+    private static java.sql.Date dt( String s)
+    {
+        return java.sql.Date.valueOf( s);
+    }
+
+    private static java.sql.Time tm( String s)
+    {
+        return java.sql.Time.valueOf( s);
+    }
+
+    private static String dateTimeToLiteral( Object ts)
+    {
+        if( ts instanceof java.sql.Timestamp)
+            return "{ts '" + ((java.sql.Timestamp)ts).toString() + "'}";
+        else if( ts instanceof java.sql.Time)
+            return "{t '" + ((java.sql.Time)ts).toString() + "'}";
+        else if( ts instanceof java.sql.Date)
+            return "{d '" + ((java.sql.Date)ts).toString() + "'}";
+        else if( ts instanceof String)
+            return "TIMESTAMP( '" + ((String) ts) + "')";
+        else
+            return ts.toString();
+    }
+
+    public static void main( String[] args)
+    {
+        System.out.println("Test timestamp arithmetic starting.");
+		try
+        {
+            timestampArith tester = new timestampArith( args);
+            tester.doIt();
+            if( tester.errorCount == 0)
+                System.out.println( "PASSED.");
+            else if( tester.errorCount == 1)
+                System.out.println( "FAILED. 1 error.");
+            else
+                System.out.println( "FAILED. " + tester.errorCount + " errors.");
+        }
+        catch( SQLException sqle)
+        {
+            reportSQLException( sqle);
+            System.exit(1);
+        }
+        catch( Exception e)
+        {
+            System.out.println("Unexpected exception: " + e.getMessage());
+            e.printStackTrace();
+            System.exit(1);
+        }
+        System.exit(0);
+    } // end of main
+
+    String composeSqlStr( String fn, int interval, String parm1, String parm2)
+    {
+        return "values( {fn TIMESTAMP" + fn + "( " + intervalJdbcNames[interval] +
+          ", " + parm1 + "," + parm2 + ")})";
+    }
+    
+    private timestampArith( String[] args) throws Exception
+    {
+        // make the initial connection.
+        ij.getPropertyArg(args);
+        conn = ij.startJBMS();
+
+        conn.setAutoCommit(false);
+        for( int i = 0; i &lt; intervalJdbcNames.length; i++)
+        {
+            tsAddPS[i] = conn.prepareStatement( composeSqlStr( "ADD", i, "?", "?"));
+            tsDiffPS[i] = conn.prepareStatement( composeSqlStr( "DIFF", i, "?", "?"));
+        }
+        stmt = conn.createStatement();
+    }
+
+    private void doIt() throws SQLException
+    {
+        for( int i = 0; i &lt; tests.length; i++)
+            tests[i].runTest();
+
+        testNullInputs();
+
+        for( int i = 0; i &lt; invalid.length; i++)
+        {
+            try
+            {
+                ResultSet rs = stmt.executeQuery( invalid[i][0]);
+                rs.next();
+                reportFailure( "\"" + invalid[i][0] + "\" did not throw an exception.");
+            }
+            catch( SQLException sqle)
+            {
+                checkExpectedException( sqle, invalid[i][1], invalid[i][2], "\"" + invalid[i][0] + "\"");
+            }
+        }
+
+        testInvalidArgTypes();
+    } // end of doIt
+
+    private void testInvalidArgTypes() throws SQLException
+    {
+        expectException( tsDiffPS[ HOUR_INTERVAL], ts( "2005-05-11 15:26:00"), new Double( 2.0), "XCL12",
+                         "An attempt was made to put a data value of type 'double' into a data value of type 'TIMESTAMP'.",
+                         "TIMESTAMPDIFF with double ts2");
+        expectException( tsDiffPS[ HOUR_INTERVAL], new Double( 2.0), ts( "2005-05-11 15:26:00"), "XCL12",
+                         "An attempt was made to put a data value of type 'double' into a data value of type 'TIMESTAMP'.",
+                         "TIMESTAMPDIFF with double ts1");
+
+        expectException( tsAddPS[ MINUTE_INTERVAL], new Integer(1), new Integer(-1), "XCL12",
+                         "An attempt was made to put a data value of type 'int' into a data value of type 'TIMESTAMP'.",
+                         "TIMESTAMPADD with int ts");
+        expectException( tsAddPS[ MINUTE_INTERVAL], ts( "2005-05-11 15:26:00"), ts( "2005-05-11 15:26:00"), "XCL12",
+                         "An attempt was made to put a data value of type 'java.sql.Timestamp' into a data value of type 'INTEGER'.",
+                         "TIMESTAMPADD with timestamp count");
+    } // end of testInvalidArgTypes
+
+    private void expectException( PreparedStatement ps, Object arg1, Object arg2,
+                                  String expectedSQLState, String expectedMsg, String label)
+    {
+        try
+        {
+            ps.setObject( 1, arg1);
+            ps.setObject( 2, arg2);
+            ResultSet rs = ps.executeQuery();
+            rs.next();
+            reportFailure( label + " did not throw an exception.");
+        }
+        catch( SQLException sqle) { checkExpectedException( sqle, expectedSQLState, expectedMsg, label);};
+    } // end of expectException
+
+    private void checkExpectedException( SQLException sqle, String expectedSQLState, String expectedMsg, String label)
+    {
+        if( ! expectedSQLState.equals( sqle.getSQLState()))
+            reportFailure( "Unexpected SQLState from \"" + label + "\". expected " +
+                           expectedSQLState + " got " + sqle.getSQLState());
+        else if( expectedMsg != null &amp;&amp; ! expectedMsg.equals( sqle.getMessage()))
+            reportFailure( "Unexpected message from \"" + label + "\".\n  expected \"" +
+                           expectedMsg + "\"\n  got \"" + sqle.getMessage() + "\"");
+    } // end of checkExpectedException
+
+    private void testNullInputs() throws SQLException
+    {
+        // Null inputs, each position, each type
+        tsDiffPS[ HOUR_INTERVAL].setTimestamp( 1, ts( "2005-05-11 15:26:00"));
+        tsDiffPS[ HOUR_INTERVAL].setNull( 2, Types.TIMESTAMP);
+        expectNullResult( tsDiffPS[ HOUR_INTERVAL], "TIMESTAMPDIFF with null timestamp in third argument");
+        tsDiffPS[ HOUR_INTERVAL].setNull( 2, Types.DATE);
+        expectNullResult( tsDiffPS[ HOUR_INTERVAL], "TIMESTAMPDIFF with null date in third argument");
+
+        tsDiffPS[ HOUR_INTERVAL].setTimestamp( 2, ts( "2005-05-11 15:26:00"));
+        tsDiffPS[ HOUR_INTERVAL].setNull( 1, Types.TIMESTAMP);
+        expectNullResult( tsDiffPS[ HOUR_INTERVAL], "TIMESTAMPDIFF with null timestamp in second argument");
+        tsDiffPS[ HOUR_INTERVAL].setNull( 1, Types.DATE);
+        expectNullResult( tsDiffPS[ HOUR_INTERVAL], "TIMESTAMPDIFF with null date in second argument");
+
+        tsAddPS[ MINUTE_INTERVAL].setTimestamp( 2, ts( "2005-05-11 15:26:00"));
+        tsAddPS[ MINUTE_INTERVAL].setNull( 1, Types.INTEGER);
+        expectNullResult( tsAddPS[ MINUTE_INTERVAL], "TIMESTAMPADD with null integer in second argument");
+
+        tsAddPS[ MINUTE_INTERVAL].setInt( 1, 1);
+        tsAddPS[ MINUTE_INTERVAL].setNull( 2, Types.TIMESTAMP);
+        expectNullResult( tsAddPS[ MINUTE_INTERVAL], "TIMESTAMPADD with null timestamp in third argument");
+        tsAddPS[ MINUTE_INTERVAL].setNull( 2, Types.DATE);
+        expectNullResult( tsAddPS[ MINUTE_INTERVAL], "TIMESTAMPADD with null date in third argument");
+    } // end of testNullInputs
+
+    private void expectNullResult( PreparedStatement ps, String label)
+    {
+        try
+        {
+            ResultSet rs = ps.executeQuery();
+            if( ! rs.next())
+                reportFailure( label + " returned no rows.");
+            else if( rs.getObject( 1) != null)
+                reportFailure( label + " did not return null.");
+        }
+        catch (SQLException sqle)
+        {
+            reportFailure( "Unexpected exception from " + label);
+            reportSQLException( sqle);
+        }
+    } // end of expectNullResult
+
+    private static void reportSQLException( SQLException sqle)
+    {
+        System.out.println("Unexpected exception:");
+        for(;;)
+        {
+            System.out.println( "    " + sqle.getMessage());
+            if( sqle.getNextException() != null)
+                sqle = sqle.getNextException();
+            else
+                break;
+        }
+        sqle.printStackTrace();
+    } // end of reportSQLException
+
+    private void reportFailure( String msg)
+    {
+        errorCount++;
+        System.out.println( msg);
+    }
+
+    private static void setDateTime( PreparedStatement ps, int parameterIdx, java.util.Date dateTime)
+        throws SQLException
+    {
+        if( dateTime instanceof java.sql.Timestamp)
+            ps.setTimestamp( parameterIdx, (java.sql.Timestamp) dateTime);
+        else if( dateTime instanceof java.sql.Date)
+            ps.setDate( parameterIdx, (java.sql.Date) dateTime);
+        else if( dateTime instanceof java.sql.Time)
+            ps.setTime( parameterIdx, (java.sql.Time) dateTime);
+        else
+            ps.setTimestamp( parameterIdx, (java.sql.Timestamp) dateTime);
+    }
+    
+    abstract class OneTest
+    {
+        final int interval; // FRAC_SECOND_INTERVAL, SECOND_INTERVAL, ... or YEAR_INTERVAL
+        final String expectedSQLState; // Null if no SQLException is expected
+        final String expectedMsg; // Null if no SQLException is expected
+        String sql;
+        
+        OneTest( int interval, String expectedSQLState, String expectedMsg)
+        {
+            this.interval = interval;
+            this.expectedSQLState = expectedSQLState;
+            this.expectedMsg = expectedMsg;
+        }
+        
+        void runTest()
+        {
+            sql = composeSQL();
+            ResultSet rs = null;
+            try
+            {
+                rs = stmt.executeQuery( sql);
+                checkResultSet( rs, sql);
+                if( expectedSQLState != null)
+                    reportFailure( "Statement '" + sql + "' did not generate an exception");
+            }
+            catch( SQLException sqle)
+            {
+                checkSQLException( "Statement", sqle);
+            }
+            if( rs != null)
+            {
+                try
+                {
+                    rs.close();
+                }
+                catch( SQLException sqle){};
+                rs = null;
+            }
+            
+            try
+            {
+                rs = executePS();
+                checkResultSet( rs, sql);
+                if( expectedSQLState != null)
+                    reportFailure( "PreparedStatement '" + sql + "' did not generate an exception");
+            }
+            catch( SQLException sqle)
+            {
+                checkSQLException( "PreparedStatement", sqle);
+            }
+            if( rs != null)
+            {
+                try
+                {
+                    rs.close();
+                }
+                catch( SQLException sqle){};
+                rs = null;
+            }
+        } // end of RunTest
+
+        private void checkResultSet( ResultSet rs, String sql) throws SQLException
+        {
+            if( rs.next())
+            {
+                checkResultRow( rs, sql);
+                if( rs.next())
+                    reportFailure( "'" + sql + "' returned more than one row.");
+            }
+            else
+                reportFailure( "'" + sql + "' did not return any rows.");
+        } // end of checkResultSet
+
+        private void checkSQLException( String type, SQLException sqle)
+        {
+            if( expectedSQLState != null)
+            {
+                if( ! expectedSQLState.equals( sqle.getSQLState()))
+                    reportFailure( "Incorrect SQLState from " + type + " '" + sql + "' expected " + expectedSQLState +
+                                   " got " + sqle.getSQLState());
+                else if( expectedMsg != null &amp;&amp; ! expectedMsg.equals( sqle.getMessage()))
+                    reportFailure( "Incorrect exception message from " + type + " '" + sql + "' expected '" + expectedMsg +
+                                   "' got '" + sqle.getMessage() + "'");
+            }
+            else
+            {
+                reportFailure( "Unexpected exception from " + type + " '" + sql + "'");
+                reportSQLException( sqle);
+            }
+        } // end of checkSQLException
+
+        abstract String composeSQL();
+
+        abstract void checkResultRow( ResultSet rs, String sql) throws SQLException;
+
+        abstract ResultSet executePS() throws SQLException;
+    }
+
+    class OneDiffTest extends OneTest
+    {
+        private final java.util.Date ts1;
+        private final java.util.Date ts2;
+        final int expectedDiff;
+        protected boolean expectNull;
+
+        OneDiffTest( int interval,
+                     java.util.Date ts1,
+                     java.util.Date ts2,
+                     int expectedDiff,
+                     String expectedSQLState,
+                     String expectedMsg)
+        {
+            super( interval, expectedSQLState, expectedMsg);
+            this.ts1 = ts1;
+            this.ts2 = ts2;
+            this.expectedDiff = expectedDiff;
+            expectNull = (ts1 == null) || (ts2 == null);
+        }
+
+        String composeSQL()
+        {
+            return composeSqlStr( "DIFF", interval, dateTimeToLiteral( ts1), dateTimeToLiteral( ts2));
+        }
+        
+        void checkResultRow( ResultSet rs, String sql) throws SQLException
+        {
+            int actualDiff = rs.getInt(1);
+            if( rs.wasNull())
+            {
+                if( !expectNull)
+                    reportFailure( "Unexpected null result from '" + sql + "'.");
+            }
+            else
+            {
+                if( expectNull)
+                    reportFailure( "Expected null result from '" + sql + "'.");
+                else if( actualDiff != expectedDiff)
+                    reportFailure( "Unexpected result from '" + sql + "'.  Expected " +
+                        expectedDiff + " got " + actualDiff + ".");
+            }
+        }
+
+        ResultSet executePS() throws SQLException
+        {
+            setDateTime( tsDiffPS[interval], 1, ts1);
+            setDateTime( tsDiffPS[interval], 2, ts2);
+            return tsDiffPS[interval].executeQuery();
+        }
+    } // end of class OneDiffTest
+
+    class OneStringDiffTest extends OneDiffTest
+    {
+        private final String ts1;
+        private final String ts2;
+
+        OneStringDiffTest( int interval,
+                           String ts1,
+                           String ts2,
+                           int expectedDiff,
+                           String expectedSQLState,
+                           String expectedMsg)
+        {
+            super( interval, (java.util.Date) null, (java.util.Date) null, expectedDiff, expectedSQLState, expectedMsg);
+            this.ts1 = ts1;
+            this.ts2 = ts2;
+            expectNull = (ts1 == null) || (ts2 == null);
+        }
+
+        String composeSQL()
+        {
+            return composeSqlStr( "DIFF", interval, dateTimeToLiteral( ts1), dateTimeToLiteral( ts2));
+        }
+
+        ResultSet executePS() throws SQLException
+        {
+            tsDiffPS[interval].setString( 1, ts1);
+            tsDiffPS[interval].setString( 2, ts2);
+            return tsDiffPS[interval].executeQuery();
+        }
+    } // end of class OneStringDiffTest
+
+    class OneAddTest extends OneTest
+    {
+        private final java.util.Date ts;
+        final int count;
+        final java.sql.Timestamp expected;
+
+        OneAddTest( int interval,
+                    int count,
+                    java.util.Date ts,
+                    java.sql.Timestamp expected,
+                    String expectedSQLState,
+                    String expectedMsg)
+        {
+            super( interval, expectedSQLState, expectedMsg);
+            this.count = count;
+            this.ts = ts;
+            this.expected = expected;
+        }
+
+        String composeSQL()
+        {
+            return composeSqlStr( "ADD", interval, String.valueOf( count), dateTimeToLiteral( ts));
+        }
+
+        void checkResultRow( ResultSet rs, String sql) throws SQLException
+        {
+            java.sql.Timestamp actual = rs.getTimestamp( 1);
+            if( rs.wasNull() || actual == null)
+            {
+                if( expected != null)
+                    reportFailure( "Unexpected null result from '" + sql + "'.");
+            }
+            else
+            {
+                if( expected == null)
+                    reportFailure( "Expected null result from '" + sql + "'.");
+                else if( ! actual.equals( expected))
+                    reportFailure( "Unexpected result from '" + sql + "'.  Expected " +
+                                   expected.toString() + " got " + actual.toString() + ".");
+            }
+        }
+
+        ResultSet executePS() throws SQLException
+        {
+            tsAddPS[interval].setInt( 1, count);
+            setDateTime( tsAddPS[interval], 2, ts);
+            return tsAddPS[interval].executeQuery();
+        }
+    } // end of class OneAddTest
+
+    class OneStringAddTest extends OneAddTest
+    {
+        private final String ts;
+
+        OneStringAddTest( int interval,
+                          int count,
+                          String ts,
+                          java.sql.Timestamp expected,
+                          String expectedSQLState,
+                          String expectedMsg)
+        {
+            super( interval, count, (java.util.Date) null, expected, expectedSQLState, expectedMsg);
+            this.ts = ts;
+        }
+
+        String composeSQL()
+        {
+            return composeSqlStr( "ADD", interval, String.valueOf( count), dateTimeToLiteral( ts));
+        }
+
+        ResultSet executePS() throws SQLException
+        {
+            tsAddPS[interval].setInt( 1, count);
+            tsAddPS[interval].setString( 2, ts);
+            return tsAddPS[interval].executeQuery();
+        }
+    } // end of class OneStringAddTest
+}
Index: java/testing/org/apache/derbyTesting/functionTests/master/timestampArith.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/timestampArith.out	(revision 0)
+++ java/testing/org/apache/derbyTesting/functionTests/master/timestampArith.out	(revision 0)
@@ -0,0 +1,2 @@
+Test timestamp arithmetic starting.
+PASSED.
Index: java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall	(revision 170691)
+++ java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall	(working copy)
@@ -123,6 +123,7 @@
 lang/supersimple.sql
 lang/syscat.sql
 lang/tempRestrictions.sql
+lang/timestampArith.java
 lang/triggerBeforeTrig.sql
 lang/triggerGeneral.sql
 lang/triggerRecursion.sql
  </pre>
</blockquote>
</body>
</html>


Mime
View raw message