You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by ba...@apache.org on 2005/05/24 04:33:38 UTC
svn commit: r178061 - in /incubator/derby/code/trunk/java:
engine/org/apache/derby/iapi/reference/
engine/org/apache/derby/iapi/sql/compile/ engine/org/apache/derby/iapi/types/
engine/org/apache/derby/impl/sql/compile/ engine/org/apache/derby/loc/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/suites/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: bandaram
Date: Mon May 23 19:33:37 2005
New Revision: 178061
URL: http://svn.apache.org/viewcvs?rev=178061&view=rev
Log:
Derby-81: Add support for JDBC escape functions timestampAdd and timestampDiff.
The syntax is of TIMESTAMPADD and TIMESTAMPDIFF is
{fn TIMESTAMPADD( interval, count, ts1)}
{fn TIMESTAMPDIFF( interval, ts1, ts2)}
where interval is one of 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, or SQL_TSI_YEAR; count is a numeric expression; and ts1 and ts2 are datetime expressions (date, time, or timestamp). If a date is used in ts1 or ts2 it is converted to a timestamp by using time 00:00:00. If a time is used in ts1 or ts2 it is converted to a timestamp by using the current date.
SQL_TSI_FRAC_SECOND indicates nanoseconds. The other interval names are self-explanatory.
TIMESTAMPADD produces a new timestamp by adding count intervals to ts1. For example
VALUES( {fn TIMESTAMPADD( SQL_TSI_DAY, 1, CURRENT_TIMESTAMP)})
produces the timestamp for one day from now.
TIMESTAMPDIFF returns the number of intervals by which ts2 exceeds ts2. It is approximately ts2 - ts1. TIMESTAMPDIFF produces an integer. If the actual difference is too large to fit in an integer then an SQLException is thrown. If the actual difference is not an integral number of intervals then TIMESTAMPDIFF rounds to 0. For example
VALUES( {fn TIMESTAMPDIFF( SQL_TSI_HOUR, {t '12:00:00'}, {t '13:50:00'})},
{fn TIMESTAMPDIFF( SQL_TSI_HOUR, {t '13:50:00'}, {t '12:00:00'})}
produces the row (1, -1).
More examples:
SELECT * FROM t WHERE {fn TIMESTAMPDIFF( SQL_TSI_DAY, CURRENT_DAY, promisedDate)} <= 1
Selects all rows from t with promisedDate at most one day from now. (It also selects rows with a promisedDate in the past). Note that this is probably not the optimal way to express the query. If promisedDate is indexed the Derby optimizer will not use the index. Better is
SELECT * FROM t WHERE promisedDate <= {fn TIMESTAMPADD( SQL_TSI_DAY, 1, CURRENT_DAY)
Submitted by Jack Klebanoff (klebanoff-derby@sbcglobal.net)
Added:
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/timestampArith.out (with props)
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/timestampArith.java (with props)
Modified:
incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/reference/SQLState.java
incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DateTimeDataValue.java
incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLDate.java
incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTime.java
incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTimestamp.java
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
incubator/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties
incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/reference/SQLState.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/reference/SQLState.java?rev=178061&r1=178060&r2=178061&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/reference/SQLState.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/reference/SQLState.java Mon May 23 19:33:37 2005
@@ -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";
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java?rev=178061&r1=178060&r2=178061&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java Mon May 23 19:33:37 2005
@@ -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;
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DateTimeDataValue.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DateTimeDataValue.java?rev=178061&r1=178060&r2=178061&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DateTimeDataValue.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/DateTimeDataValue.java Mon May 23 19:33:37 2005
@@ -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;
}
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLDate.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLDate.java?rev=178061&r1=178060&r2=178061&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLDate.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLDate.java Mon May 23 19:33:37 2005
@@ -1007,4 +1007,56 @@
ps.setDate(position, getDate((Calendar) null));
}
+
+ /**
+ * 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);
+ }
}
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTime.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTime.java?rev=178061&r1=178060&r2=178061&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTime.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTime.java Mon May 23 19:33:37 2005
@@ -975,5 +975,59 @@
ps.setTime(position, getTime((Calendar) null));
}
+
+ /**
+ * 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);
+ }
}
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTimestamp.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTimestamp.java?rev=178061&r1=178060&r2=178061&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTimestamp.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTimestamp.java Mon May 23 19:33:37 2005
@@ -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 >= 0 && nanos < ONE_BILLION)
+ tsResult.nanos = (int) nanos;
+ else
+ {
+ int secondsInc = (int)(nanos/ONE_BILLION);
+ if( nanos >= 0)
+ tsResult.nanos = (int) (nanos % ONE_BILLION);
+ else
+ {
+ secondsInc--;
+ nanos -= secondsInc * (long)ONE_BILLION; // 0 <= nanos < 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 && state.length() > 0 && 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 <= 0 or both >= 0.
+ if( nanosDiff < 0 && secondsDiff > 0)
+ {
+ secondsDiff--;
+ nanosDiff += ONE_BILLION;
+ }
+ else if( nanosDiff > 0 && secondsDiff < 0)
+ {
+ secondsDiff++;
+ nanosDiff -= ONE_BILLION;
+ }
+ long ldiff = 0;
+
+ switch( intervalType)
+ {
+ case FRAC_SECOND_INTERVAL:
+ if( secondsDiff > Integer.MAX_VALUE/ONE_BILLION || secondsDiff < 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) > 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 >= 0)
+ {
+ if (ldiff >= 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 > thisInSeconds)
+ break;
+ cal.add( Calendar.MONTH, 1);
+ ldiff++;
+ }
+ }
+ else
+ {
+ if (ldiff <= 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 < 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 >= 0)
+ {
+ if (ldiff >= 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 > thisInSeconds)
+ break;
+ cal.add( Calendar.YEAR, 1);
+ ldiff++;
+ }
+ }
+ else
+ {
+ if (ldiff <= 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 < thisInSeconds)
+ break;
+ cal.add( Calendar.YEAR, -1);
+ ldiff--;
+ }
+ }
+ break;
+
+ default:
+ throw StandardException.newException( SQLState.LANG_INVALID_FUNCTION_ARGUMENT,
+ ReuseFactory.getInteger( intervalType),
+ "TIMESTAMPDIFF");
+ }
+ if (ldiff > Integer.MAX_VALUE || ldiff < 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
}
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java?rev=178061&r1=178060&r2=178061&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java Mon May 23 19:33:37 2005
@@ -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);
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java?rev=178061&r1=178060&r2=178061&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java Mon May 23 19:33:37 2005
@@ -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 && 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);
/*
@@ -780,6 +806,72 @@
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))
+ {
+ int jdbcType = rightOperand.getTypeId().getJDBCTypeId();
+ if( jdbcType != Types.TINYINT && jdbcType != Types.SMALLINT &&
+ jdbcType != Types.INTEGER && jdbcType != Types.BIGINT)
+ 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() && arg.getTypeId() == null)
+ {
+ ((ParameterNode) arg).setDescriptor( new DataTypeDescriptor(TypeId.getBuiltInTypeId( jdbcType), true));
+ return true;
+ }
+ return false;
+ } // end of bindParameter
public ValueNode getReceiver()
{
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj?rev=178061&r1=178060&r2=178061&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj Mon May 23 19:33:37 2005
@@ -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 @@
| <SAVEPOINT: "savepoint">
| <SCALE: "scale">
| <SERIALIZABLE: "serializable">
+| <SQL_TSI_FRAC_SECOND: "sql_tsi_frac_second">
+| <SQL_TSI_SECOND: "sql_tsi_second">
+| <SQL_TSI_MINUTE: "sql_tsi_minute">
+| <SQL_TSI_HOUR: "sql_tsi_hour">
+| <SQL_TSI_DAY: "sql_tsi_day">
+| <SQL_TSI_WEEK: "sql_tsi_week">
+| <SQL_TSI_MONTH: "sql_tsi_month">
+| <SQL_TSI_QUARTER: "sql_tsi_quarter">
+| <SQL_TSI_YEAR: "sql_tsi_year">
| <START: "start">
| <STATEMENT: "statement">
| <THEN: "then">
| <TIME: "time">
| <TIMESTAMP: "timestamp">
+| <TIMESTAMPADD: "timestampadd">
+| <TIMESTAMPDIFF: "timestampdiff">
| <TRUNCATE: "truncate">
| <TYPE: "type">
| <UNCOMMITTED: "uncommitted">
@@ -5473,7 +5490,103 @@
{
return value;
}
+|
+ value = timestampArithmeticFuncion()
+ {
+ return value;
+ }
+}
+/*
+ * <A NAME="timestampArithmeticFuncion">timestampArithmeticFuncion</A>
+ */
+ValueNode
+timestampArithmeticFuncion() throws StandardException :
+{
+ ValueNode intervalType;
+ ValueNode tstamp1;
+ ValueNode tstamp2;
+ ValueNode count;
+}
+{
+ <TIMESTAMPADD> <LEFT_PAREN> intervalType = jdbcIntervalType() <COMMA>
+ count = additiveExpression(null,0,false) <COMMA>
+ tstamp1 = additiveExpression(null,0,false) <RIGHT_PAREN>
+ {
+ return (ValueNode) nodeFactory.getNode( C_NodeTypes.TIMESTAMP_ADD_FN_NODE,
+ tstamp1,
+ intervalType,
+ count,
+ ReuseFactory.getInteger( TernaryOperatorNode.TIMESTAMPADD),
+ null,
+ getContextManager());
+ }
+|
+ <TIMESTAMPDIFF> <LEFT_PAREN> intervalType = jdbcIntervalType() <COMMA>
+ tstamp1 = additiveExpression(null,0,false) <COMMA>
+ tstamp2 = additiveExpression(null,0,false) <RIGHT_PAREN>
+ {
+ return (ValueNode) nodeFactory.getNode( C_NodeTypes.TIMESTAMP_DIFF_FN_NODE,
+ tstamp2,
+ intervalType,
+ tstamp1,
+ ReuseFactory.getInteger( TernaryOperatorNode.TIMESTAMPDIFF),
+ null,
+ getContextManager());
+ }
+}
+
+/*
+ * <A NAME="jdbcIntervalType">jdbcIntervalType</A>
+ */
+ValueNode jdbcIntervalType() throws StandardException :
+{
+}
+{
+ <SQL_TSI_FRAC_SECOND>
+ {
+ return getJdbcIntervalNode( DateTimeDataValue.FRAC_SECOND_INTERVAL);
+ }
+|
+ <SQL_TSI_SECOND>
+ {
+ return getJdbcIntervalNode( DateTimeDataValue.SECOND_INTERVAL);
+ }
+|
+ <SQL_TSI_MINUTE>
+ {
+ return getJdbcIntervalNode( DateTimeDataValue.MINUTE_INTERVAL);
+ }
+|
+ <SQL_TSI_HOUR>
+ {
+ return getJdbcIntervalNode( DateTimeDataValue.HOUR_INTERVAL);
+ }
+|
+ <SQL_TSI_DAY>
+ {
+ return getJdbcIntervalNode( DateTimeDataValue.DAY_INTERVAL);
+ }
+|
+ <SQL_TSI_WEEK>
+ {
+ return getJdbcIntervalNode( DateTimeDataValue.WEEK_INTERVAL);
+ }
+|
+ <SQL_TSI_MONTH>
+ {
+ return getJdbcIntervalNode( DateTimeDataValue.MONTH_INTERVAL);
+ }
+|
+ <SQL_TSI_QUARTER>
+ {
+ return getJdbcIntervalNode( DateTimeDataValue.QUARTER_INTERVAL);
+ }
+|
+ <SQL_TSI_YEAR>
+ {
+ return getJdbcIntervalNode( DateTimeDataValue.YEAR_INTERVAL);
+ }
}
/*
@@ -11411,6 +11524,15 @@
| tok = <SHARE>
| tok = <SPECIFIC>
| tok = <SQLID>
+ | tok = <SQL_TSI_FRAC_SECOND>
+ | tok = <SQL_TSI_SECOND>
+ | tok = <SQL_TSI_MINUTE>
+ | tok = <SQL_TSI_HOUR>
+ | tok = <SQL_TSI_DAY>
+ | tok = <SQL_TSI_WEEK>
+ | tok = <SQL_TSI_MONTH>
+ | tok = <SQL_TSI_QUARTER>
+ | tok = <SQL_TSI_YEAR>
| tok = <SQRT>
| tok = <STABILITY>
| tok = <START>
@@ -11420,6 +11542,8 @@
| tok = <THEN>
| tok = <TIME>
| tok = <TIMESTAMP>
+ | tok = <TIMESTAMPADD>
+ | tok = <TIMESTAMPDIFF>
| tok = <TRIGGER>
| tok = <TRUNCATE>
| tok = <TS>
Modified: incubator/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties?rev=178061&r1=178060&r2=178061&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties (original)
+++ incubator/derby/code/trunk/java/engine/org/apache/derby/loc/messages_en.properties Mon May 23 19:33:37 2005
@@ -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}.
Added: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/timestampArith.out
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/timestampArith.out?rev=178061&view=auto
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/timestampArith.out (added)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/timestampArith.out Mon May 23 19:33:37 2005
@@ -0,0 +1,2 @@
+Test timestamp arithmetic starting.
+PASSED.
Propchange: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/timestampArith.out
------------------------------------------------------------------------------
svn:eol-style = native
Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?rev=178061&r1=178060&r2=178061&view=diff
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall Mon May 23 19:33:37 2005
@@ -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
Added: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/timestampArith.java
URL: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/timestampArith.java?rev=178061&view=auto
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/timestampArith.java (added)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/timestampArith.java Mon May 23 19:33:37 2005
@@ -0,0 +1,789 @@
+/*
+
+ 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
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ 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 && 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 < 4; i++)
+ sb.append( '0');
+ sb.append( s);
+ sb.append( '-');
+
+ s = String.valueOf( cal.get( Calendar.MONTH) + 1);
+ for( int i = s.length(); i < 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 < 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.1, CURRENT_TIMESTAMP)})", "42X45",
+ "DECIMAL 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() < 29)
+ {
+ // Pad out the fraction with zeros
+ StringBuffer sb = new StringBuffer( s);
+ if( s.length() == 19)
+ sb.append( '.');
+ while( sb.length() < 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 < 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 < tests.length; i++)
+ tests[i].runTest();
+
+ testNullInputs();
+
+ for( int i = 0; i < 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 && ! 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 && ! 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
+}
Propchange: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/timestampArith.java
------------------------------------------------------------------------------
svn:eol-style = native