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] Conversions to Date and Timestamp
Date Thu, 26 May 2005 20:34:05 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">
Submitted this patch. Nicely done patch. I added new
UnaryDateTimestampOperatorNode to the list of&nbsp; nodes in
DBMSnodes.properties, so that Derby JAR build will pick up the class.
Otherwise, I suspect tests on JAR build might fail.<br>
<br>
Satheesh<br>
<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\iapi\reference\SQLState.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\iapi\sql\compile\C_NodeTypes.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\iapi\types\DataValueFactory.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\iapi\types\DataValueFactoryImpl.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\iapi\types\SQLDate.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\iapi\types\SQLTime.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\iapi\types\SQLTimestamp.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\C_NodeNames.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\NodeFactoryImpl.java<br>
Adding&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\UnaryDateTimestampOperatorNode.java<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java\engine\org\apache\derby\loc\messages_en.properties<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\master\datetime.out<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java\testing\org\apache\derbyTesting\functionTests\tests\lang\datetime.sql<br>
Sending&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; tools\jar\DBMSnodes.properties<br>
Transmitting file data ...............<br>
Committed revision 164873.<br>
<br>
Jack Klebanoff wrote:
<blockquote cite="mid426D2A0D.9070706@sbcglobal.net" type="cite">The
attached path contains some fixes to conversions to dates and
timestamps.
  <br>
1. According to the Derby documentation the DATE and TIMESTAMP
functions provide some conversions beyond those handled by the normal
casts. This patch implements them. The DATE function converts a numeric
argument to a date by taking the integer portion of the number and
considering it as the number of days since Jan. 1 1970. The DATE
function also handles a string of length 7 in the format 'yyyyddd'. It
is taken to indicate the ddd'th day of year yyyy. The TIMESTAMP
function handles string arguments of length 14 in the format
'yyyyMMddhhmmss'. Any other arguments to the DATE and TIMESTAMP
functions are handled as normal casts to date or timestamp.
  <br>
  <br>
(The TIME function does not do any special conversions. It is the same
as the CAST function).
  <br>
  <br>
2. The string to time cast has been made more lenient by making the
seconds field optional.
  <br>
  <br>
Previously the DATE and unary TIMESTAMP functions were implemented by
converting them to calls to CAST. This was done in the parser. The
patch changes this. The parser now generates a
UnaryDateTimestampOperatorNode to represent these functions. The
generated code calls new DataValueFactory methods. Most of the actual
work is done in the SQLDate and SQLTimestamp classes.
  <br>
  <br>
The patch passed the derbyall suite.
  <br>
  <br>
Jack Klebanoff
  <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 164436)
+++ java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java	(working copy)
@@ -554,6 +554,9 @@
 		  case C_NodeTypes.SAVEPOINT_NODE:
 		  	return C_NodeNames.SAVEPOINT_NODE_NAME;
 
+          case C_NodeTypes.UNARY_DATE_TIMESTAMP_OPERATOR_NODE:
+            return C_NodeNames.UNARY_DATE_TIMESTAMP_OPERATOR_NODE_NAME;
+
 		  case C_NodeTypes.TIMESTAMP_OPERATOR_NODE:
             return C_NodeNames.TIMESTAMP_OPERATOR_NODE_NAME;
 
Index: java/engine/org/apache/derby/impl/sql/compile/UnaryDateTimestampOperatorNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/UnaryDateTimestampOperatorNode.java	(revision
0)
+++ java/engine/org/apache/derby/impl/sql/compile/UnaryDateTimestampOperatorNode.java	(revision
0)
@@ -0,0 +1,196 @@
+/*
+
+   Derby - Class org.apache.derby.impl.sql.compile.UnaryDateTimestampOperatorNode
+
+   Copyright 2004 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.derby.impl.sql.compile;
+
+import org.apache.derby.iapi.types.DataValueFactory;
+import org.apache.derby.iapi.types.DataTypeDescriptor;
+import org.apache.derby.iapi.types.DataValueDescriptor;
+import org.apache.derby.iapi.types.DateTimeDataValue;
+import org.apache.derby.iapi.services.compiler.MethodBuilder;
+import org.apache.derby.iapi.error.StandardException;
+
+import org.apache.derby.iapi.sql.compile.C_NodeTypes;
+
+import org.apache.derby.impl.sql.compile.ExpressionClassBuilder;
+
+import org.apache.derby.iapi.reference.ClassName;
+import org.apache.derby.iapi.reference.SQLState;
+
+import org.apache.derby.iapi.services.classfile.VMOpcode;
+import org.apache.derby.iapi.services.sanity.SanityManager;
+
+import java.sql.Types;
+
+import java.util.Vector;
+
+/**
+ * This class implements the timestamp( x) and date(x) functions.
+ *
+ * These two functions implement a few special cases of string conversions beyond the normal
string to
+ * date/timestamp casts.
+ */
+public class UnaryDateTimestampOperatorNode extends UnaryOperatorNode
+{
+    private static final String TIMESTAMP_METHOD_NAME = "getTimestamp";
+    private static final String DATE_METHOD_NAME = "getDate";
+    
+    /**
+     * @param operand The operand of the function
+     * @param targetType The type of the result. Timestamp or Date.
+     *
+	 * @exception StandardException		Thrown on error
+	 */
+
+	public void init( Object operand, Object targetType)
+		throws StandardException
+	{
+		setType( (DataTypeDescriptor) targetType);
+        switch( getTypeServices().getJDBCTypeId())
+        {
+        case Types.DATE:
+            super.init( operand, "date", DATE_METHOD_NAME);
+            break;
+
+        case Types.TIMESTAMP:
+            super.init( operand, "timestamp", TIMESTAMP_METHOD_NAME);
+            break;
+
+        default:
+            if( SanityManager.DEBUG)
+                SanityManager.NOTREACHED();
+            super.init( operand);
+        }
+    }
+    
+    /**
+     * Called by UnaryOperatorNode.bindExpression.
+     *
+     * If the operand is a constant then evaluate the function at compile time. Otherwise,
+     * if the operand input type is the same as the output type then discard this node altogether.
+     * If the function is "date" and the input is a timestamp then change this node to a
cast.
+     *
+	 * @param fromList		The FROM list for the query this
+	 *				expression is in, for binding columns.
+	 * @param subqueryList		The subquery list being built as we find SubqueryNodes
+	 * @param aggregateVector	The aggregate vector being built as we find AggregateNodes
+	 *
+	 * @return	The new top of the expression tree.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+	protected ValueNode bindUnaryOperator(
+					FromList fromList, SubqueryList subqueryList,
+					Vector aggregateVector)
+				throws StandardException
+	{
+        boolean isIdentity = false; // Is this function the identity operator?
+        boolean operandIsNumber = false;
+        
+        super.bindUnaryOperator( fromList, subqueryList, aggregateVector);
+        DataTypeDescriptor operandType = operand.getTypeServices();
+        switch( operandType.getJDBCTypeId())
+        {
+        case Types.BIGINT:
+        case Types.INTEGER:
+        case Types.SMALLINT:
+        case Types.TINYINT:
+        case Types.DECIMAL:
+        case Types.NUMERIC:
+        case Types.DOUBLE:
+        case Types.FLOAT:
+            if( TIMESTAMP_METHOD_NAME.equals( methodName))
+                invalidOperandType();
+            operandIsNumber = true;
+            break;
+            
+        case Types.CHAR:
+        case Types.VARCHAR:
+            break;
+
+        case Types.DATE:
+            if( TIMESTAMP_METHOD_NAME.equals( methodName))
+                invalidOperandType();
+            isIdentity = true;
+            break;
+            
+        case Types.NULL:
+            break;
+           
+        case Types.TIMESTAMP:
+            if( TIMESTAMP_METHOD_NAME.equals( methodName))
+                isIdentity = true;
+            break;
+
+        default:
+            invalidOperandType();
+        }
+       
+        if( operand instanceof ConstantNode)
+        {
+            DataValueFactory dvf = getLanguageConnectionContext().getDataValueFactory();
+            DataValueDescriptor sourceValue = ((ConstantNode) operand).getValue();
+            DataValueDescriptor destValue = null;
+            if( sourceValue.isNull())
+            {
+                destValue = (TIMESTAMP_METHOD_NAME.equals( methodName))
+                ? dvf.getNullTimestamp( (DateTimeDataValue) null)
+                : dvf.getNullDate( (DateTimeDataValue) null);
+            }
+            else
+            {
+                destValue = (TIMESTAMP_METHOD_NAME.equals( methodName))
+                  ? dvf.getTimestamp( sourceValue) : dvf.getDate( sourceValue);
+            }
+            return (ValueNode) getNodeFactory().getNode( C_NodeTypes.USERTYPE_CONSTANT_NODE,
+                                                         destValue, getContextManager());
+        }
+
+        if( isIdentity)
+            return operand;
+        return this;
+    } // end of bindUnaryOperator
+
+    private void invalidOperandType() throws StandardException
+    {
+        throw StandardException.newException( SQLState.LANG_UNARY_FUNCTION_BAD_TYPE,
+                                              getOperatorString(), getOperand().getTypeServices().getSQLstring());
+    }
+
+	/**
+	 * Do code generation for this unary operator.
+	 *
+	 * @param acb	The ExpressionClassBuilder for the class we're generating
+	 * @param mb	The method the expression will go into
+	 *
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+
+	public void generateExpression( ExpressionClassBuilder acb,
+                                    MethodBuilder mb)
+        throws StandardException
+	{
+        acb.pushDataValueFactory( mb);
+        operand.generateExpression( acb, mb);
+        mb.cast( ClassName.DataValueDescriptor);
+        mb.callMethod( VMOpcode.INVOKEINTERFACE, (String) null, methodName, getTypeCompiler().interfaceName(),
1);
+    } // end of generateExpression
+}
Index: java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java	(revision 164436)
+++ java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java	(working copy)
@@ -250,6 +250,9 @@
 
 	static final String UNARY_ARITHMETIC_OPERATOR_NODE_NAME = "org.apache.derby.impl.sql.compile.UnaryArithmeticOperatorNode";
 
+	static final String UNARY_DATE_TIMESTAMP_OPERATOR_NODE_NAME
+    = "org.apache.derby.impl.sql.compile.UnaryDateTimestampOperatorNode";
+
 	static final String UNION_NODE_NAME = "org.apache.derby.impl.sql.compile.UnionNode";
 
 	static final String INTERSECT_OR_EXCEPT_NODE_NAME = "org.apache.derby.impl.sql.compile.IntersectOrExceptNode";
Index: java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj	(revision 164436)
+++ java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj	(working copy)
@@ -6081,7 +6081,7 @@
         &lt;DATE&gt; &lt;LEFT_PAREN&gt; value = additiveExpression(null,0,
false) &lt;RIGHT_PAREN&gt;
 	{
 		return (ValueNode) nodeFactory.getNode(
-							C_NodeTypes.CAST_NODE,
+							C_NodeTypes.UNARY_DATE_TIMESTAMP_OPERATOR_NODE,
                                                         value,
                                                         DataTypeDescriptor.getBuiltInDataTypeDescriptor(
Types.DATE),
                                                         getContextManager());
@@ -6115,7 +6115,7 @@
         &lt;RIGHT_PAREN&gt;
 	{
 		return (ValueNode) nodeFactory.getNode(
-							C_NodeTypes.CAST_NODE,
+							C_NodeTypes.UNARY_DATE_TIMESTAMP_OPERATOR_NODE,
                                                         firstArg,
                                                         DataTypeDescriptor.getBuiltInDataTypeDescriptor(
Types.TIMESTAMP),
                                                         getContextManager());
Index: java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
===================================================================
--- java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java	(revision 164436)
+++ java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java	(working copy)
@@ -61,7 +61,7 @@
 	static final int UNARY_MINUS_OPERATOR_NODE = 29;
 	static final int UNARY_PLUS_OPERATOR_NODE = 30;
 	static final int SQL_BOOLEAN_CONSTANT_NODE = 31;
-	// 32 is available
+	static final int UNARY_DATE_TIMESTAMP_OPERATOR_NODE = 32;
 	static final int TIMESTAMP_OPERATOR_NODE = 33;
 	static final int TABLE_NAME = 34;
 	static final int GROUP_BY_COLUMN = 35;
Index: java/engine/org/apache/derby/iapi/types/SQLDate.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/SQLDate.java	(revision 164436)
+++ java/engine/org/apache/derby/iapi/types/SQLDate.java	(working copy)
@@ -379,7 +379,7 @@
 	{
 		parseDate(value);
 	}
-
+    
     private void parseDate( java.util.Date value) throws StandardException
 	{
 		encodedDate = computeEncodedDate(value);
@@ -929,5 +929,72 @@
 		currentCal.setTime(value);
 		return SQLDate.computeEncodedDate(currentCal);
 	}
+
+
+        /**
+         * Implement the date SQL function: construct a SQL date from a string, number, or
timestamp.
+         *
+         * @param operand Must be a date or a string convertible to a date.
+         * @param dvf the DataValueFactory
+         *
+         * @exception StandardException standard error policy
+         */
+    public static DateTimeDataValue computeDateFunction( DataValueDescriptor operand,
+                                                         DataValueFactory dvf) throws StandardException
+    {
+        try
+        {
+            if( operand.isNull())
+                return new SQLDate();
+            if( operand instanceof SQLDate)
+                return (SQLDate) operand.getClone();
+
+            if( operand instanceof SQLTimestamp)
+            {
+                DateTimeDataValue retVal = new SQLDate();
+                retVal.setValue( operand);
+                return retVal;
+            }
+            if( operand instanceof NumberDataValue)
+            {
+                int daysSinceEpoch = operand.getInt();
+                if( daysSinceEpoch &lt;= 0 || daysSinceEpoch &gt; 3652059)
+                    throw StandardException.newException( SQLState.LANG_INVALID_FUNCTION_ARGUMENT,
+                                                          operand.getString(), "date");
+                Calendar cal = new GregorianCalendar( 1970, 0, 1, 12, 0, 0);
+                cal.add( Calendar.DATE, daysSinceEpoch - 1);
+                return new SQLDate( computeEncodedDate( cal.get( Calendar.YEAR),
+                                                        cal.get( Calendar.MONTH) + 1,
+                                                        cal.get( Calendar.DATE)));
+            }
+            String str = operand.getString();
+            if( str.length() == 7)
+            {
+                // yyyyddd where ddd is the day of the year
+                int year = SQLTimestamp.parseDateTimeInteger( str, 0, 4);
+                int dayOfYear = SQLTimestamp.parseDateTimeInteger( str, 4, 3);
+                if( dayOfYear &lt; 1 || dayOfYear &gt; 366)
+                    throw StandardException.newException( SQLState.LANG_INVALID_FUNCTION_ARGUMENT,
+                                                          operand.getString(), "date");
+                Calendar cal = new GregorianCalendar( year, 0, 1, 2, 0, 0);
+                cal.add( Calendar.DAY_OF_YEAR, dayOfYear - 1);
+                int y = cal.get( Calendar.YEAR);
+                if( y != year)
+                    throw StandardException.newException( SQLState.LANG_INVALID_FUNCTION_ARGUMENT,
+                                                          operand.getString(), "date");
+                return new SQLDate( computeEncodedDate( year,
+                                                        cal.get( Calendar.MONTH) + 1,
+                                                        cal.get( Calendar.DATE)));
+            }
+            // Else use the standard cast.
+            return dvf.getDateValue( str, false);
+        }
+        catch( StandardException se)
+        {
+            if( SQLState.LANG_DATE_SYNTAX_EXCEPTION.startsWith( se.getSQLState()))
+                throw StandardException.newException( SQLState.LANG_INVALID_FUNCTION_ARGUMENT,
+                                                      operand.getString(), "date");
+            throw se;
+        }
+    } // end of computeDateFunction
 }
-
Index: java/engine/org/apache/derby/iapi/types/SQLTime.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/SQLTime.java	(revision 164436)
+++ java/engine/org/apache/derby/iapi/types/SQLTime.java	(working copy)
@@ -446,7 +446,7 @@
     private static final char IBM_EUR_SEPARATOR = '.';
     private static final char[] IBM_EUR_SEPARATOR_OR_END = {IBM_EUR_SEPARATOR, (char) 0};
     static final char JIS_SEPARATOR = ':';
-    private static final char[] US_OR_JIS_MINUTE_END = {JIS_SEPARATOR, ' '};
+    private static final char[] US_OR_JIS_MINUTE_END = {JIS_SEPARATOR, ' ', (char) 0};
     private static final char[] ANY_SEPARATOR = { '.', ':', ' '};
     private static final String[] AM_PM = {"AM", "PM"};
     private static final char[] END_OF_STRING = {(char) 0};
@@ -503,8 +503,7 @@
                     second = parser.parseInt( 2, false, END_OF_STRING, false);
                     break;
 
-                default:
-                    validSyntax = false;
+                    // default is end of string, meaning that the seconds part is zero.
                 }
                 break;
 
Index: java/engine/org/apache/derby/iapi/types/DataValueFactoryImpl.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/DataValueFactoryImpl.java	(revision 164436)
+++ java/engine/org/apache/derby/iapi/types/DataValueFactoryImpl.java	(working copy)
@@ -606,6 +606,30 @@
                 return previous;
         }
 
+        /**
+         * Implement the date SQL function: construct a SQL date from a string, number, or
timestamp.
+         *
+         * @param operand Must be a date, a number, or a string convertible to a date.
+         *
+         * @exception StandardException standard error policy
+         */
+        public DateTimeDataValue getDate( DataValueDescriptor operand) throws StandardException
+        {
+                return SQLDate.computeDateFunction( operand, this);
+        }
+
+        /**
+         * Implement the timestamp SQL function: construct a SQL timestamp from a string,
or timestamp.
+         *
+         * @param operand Must be a timestamp or a string convertible to a timestamp.
+         *
+         * @exception StandardException standard error policy
+         */
+        public DateTimeDataValue getTimestamp( DataValueDescriptor operand) throws StandardException
+        {
+                return SQLTimestamp.computeTimestampFunction( operand, this);
+        }
+
         public DateTimeDataValue getTimestamp( DataValueDescriptor date, DataValueDescriptor
time) throws StandardException
         {
             return new SQLTimestamp( date, time);
Index: java/engine/org/apache/derby/iapi/types/SQLTimestamp.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/SQLTimestamp.java	(revision 164436)
+++ java/engine/org/apache/derby/iapi/types/SQLTimestamp.java	(working copy)
@@ -998,6 +998,58 @@
 
                   ps.setTimestamp(position, getTimestamp((Calendar) null));
      }
-}
 
+    /**
+     * Compute the SQL timestamp function.
+     *
+     * @exception StandardException
+     */
+    public static DateTimeDataValue computeTimestampFunction( DataValueDescriptor operand,
+                                                              DataValueFactory dvf) throws
StandardException
+    {
+        try
+        {
+            if( operand.isNull())
+                return new SQLTimestamp();
+            if( operand instanceof SQLTimestamp)
+                return (SQLTimestamp) operand.getClone();
 
+            String str = operand.getString();
+            if( str.length() == 14)
+            {
+                int year = parseDateTimeInteger( str, 0, 4);
+                int month = parseDateTimeInteger( str, 4, 2);
+                int day = parseDateTimeInteger( str, 6, 2);
+                int hour = parseDateTimeInteger( str, 8, 2);
+                int minute = parseDateTimeInteger( str, 10, 2);
+                int second = parseDateTimeInteger( str, 12, 2);
+                return new SQLTimestamp( SQLDate.computeEncodedDate( year, month, day),
+                                         SQLTime.computeEncodedTime( hour,minute,second),
+                                         0);
+            }
+            // else use the standard cast
+            return dvf.getTimestampValue( str, false);
+        }
+        catch( StandardException se)
+        {
+            if( SQLState.LANG_DATE_SYNTAX_EXCEPTION.startsWith( se.getSQLState()))
+                throw StandardException.newException( SQLState.LANG_INVALID_FUNCTION_ARGUMENT,
+                                                      operand.getString(), "timestamp");
+            throw se;
+        }
+    } // end of computeTimestampFunction
+
+    static int parseDateTimeInteger( String str, int start, int ndigits) throws StandardException
+    {
+        int end = start + ndigits;
+        int retVal = 0;
+        for( int i = start; i &lt; end; i++)
+        {
+            char c = str.charAt( i);
+            if( !Character.isDigit( c))
+                throw StandardException.newException( SQLState.LANG_DATE_SYNTAX_EXCEPTION);
+            retVal = 10*retVal + Character.digit( c, 10);
+        }
+        return retVal;
+    } // end of parseDateTimeInteger
+}
Index: java/engine/org/apache/derby/iapi/types/DataValueFactory.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/DataValueFactory.java	(revision 164436)
+++ java/engine/org/apache/derby/iapi/types/DataValueFactory.java	(working copy)
@@ -395,7 +395,15 @@
         DateTimeDataValue       getDataValue(Timestamp value,
                                                                                 DateTimeDataValue
previous)
                                                         throws StandardException;
+
         /**
+         * Implement the timestamp SQL function: construct a SQL timestamp from a string,
or timestamp.
+         *
+         * @param operand Must be a timestamp or a string convertible to a timestamp.
+         */
+        DateTimeDataValue getTimestamp( DataValueDescriptor operand) throws StandardException;
+
+        /**
          * Construct a SQL timestamp from a date and time.
          *
          * @param date Must be convertible to a date.
@@ -404,6 +412,17 @@
         DateTimeDataValue getTimestamp( DataValueDescriptor date, DataValueDescriptor time)
throws StandardException;
 
         /**
+         * Implements the SQL date function
+         *
+         * @param operand A date, timestamp, string or integer.
+         *
+         * @return the corresponding date value
+         *
+         * @exception StandardException if the syntax is invalid or the date is out of range.
+         */
+        public DateTimeDataValue getDate( DataValueDescriptor operand) throws StandardException;
+
+        /**
          * @param dateStr A date in one of the DB2 standard date formats or the local format.
          * @param isJdbcEscape If true then the timestamp must be in the JDBC timestamp escape
format, otherwise it must
          *                     be in the DB2 timestamp format.
Index: java/engine/org/apache/derby/iapi/reference/SQLState.java
===================================================================
--- java/engine/org/apache/derby/iapi/reference/SQLState.java	(revision 164436)
+++ java/engine/org/apache/derby/iapi/reference/SQLState.java	(working copy)
@@ -654,6 +654,7 @@
 
 	String LANG_DATE_RANGE_EXCEPTION                                   = "22007.S.180";
 	String LANG_DATE_SYNTAX_EXCEPTION                                  = "22007.S.181";
+    String LANG_INVALID_FUNCTION_ARGUMENT                              = "22008.S";
 	String LANG_SUBSTR_START_OR_LEN_OUT_OF_RANGE                        = "22011";
 	String LANG_DIVIDE_BY_ZERO                                         = "22012";
     String LANG_SQRT_OF_NEG_NUMBER                                     = "22013";
Index: java/engine/org/apache/derby/loc/messages_en.properties
===================================================================
--- java/engine/org/apache/derby/loc/messages_en.properties	(revision 164436)
+++ java/engine/org/apache/derby/loc/messages_en.properties	(working copy)
@@ -361,6 +361,7 @@
 22005=An attempt was made to get a data value of type ''{0}'' from a data value of type ''{1}''.
 22007.S.180=The string representation of a datetime value is out of range.
 22007.S.181=The syntax of the string representation of a datetime value is incorrect.
+22008.S=''{0}'' is an invalid argument to the {1} function.
 22011=The second or third argument of the SUBSTR function is out of range.
 22012=Attempt to divide by zero.
 22013=Attempt to take the square root of a negative number, ''{0}''.
Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/datetime.sql
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/datetime.sql	(revision 164436)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/datetime.sql	(working copy)
@@ -540,7 +540,13 @@
 insert into t values ('17.05.11  ');
 insert into t values ('17:05:11  ');
 
--- 7 rows
+-- seconds can be omitted
+insert into t values ('1:01');
+insert into t values ('1:02 ');
+insert into t values ('2.01');
+insert into t values ('2.02 ');
+
+-- 11 rows
 select * from t;
 
 delete from t;
@@ -578,3 +584,105 @@
 values date('2004-04-15-16.15.32.387');
 values date('2004-04-15-16.15.32.387 zz');
 values date('2004-04-15-16.15.32.y');
+
+values time('13:59');
+values time('1:00');
+
+-- Test unary date and datetime functions. Test with both constant and variable arguments.
+
+autocommit off;
+
+-- test date(integer)
+create table t( i int, d date);
+commit;
+
+insert into t values( 1, date(1)),(10, date(10.1)),(365,date(365.1e0)),(366,date(366)),(789,date(789)),(790,date(790)),(791,date(791));
+
+-- should fail
+insert into t values( 0, date(0));
+insert into t values( -1, date(-1));
+insert into t values( 3652060, date( 3652060));
+select i,d,date(i),date(d) from t order by i;
+
+rollback;
+insert into t(i) values( 0);
+select date(i) from t;
+
+rollback;
+insert into t(i) values( -1);
+select date(i) from t;
+
+
+rollback;
+insert into t(i) values( 3652060);
+select date(i) from t;
+
+rollback;
+
+drop table t;
+create table t( s varchar(32), d date);
+commit;
+
+insert into t values('1900060', date('1900060')),
+                    ('1904060', date('1904060')),
+                    ('1904366', date('1904366')),
+                    ('2000060', date('2000060')),
+                    ('2001060', date('2001060')),
+                    ('2001365', date('2001365'));
+select s,d,date(s) from t order by s;
+rollback;
+
+-- failure cases
+values( date('2001000'));
+values( date('2001366'));
+values( date('2000367'));
+values( date('xxxxxxx'));
+
+insert into t(s) values( '2001000');
+select date(s) from t;
+rollback;
+
+insert into t(s) values( '2001366');
+select date(s) from t;
+rollback;
+
+insert into t(s) values( '2000367');
+select date(s) from t;
+rollback;
+
+insert into t(s) values( 'xxxxxxx');
+select date(s) from t;
+rollback;
+
+-- test parameter
+prepare dateTimePS as 'values( date(cast(? as integer)),timestamp(cast(? as varchar(32))))';
+execute dateTimePS using 'values(cast(1 as integer), ''2003-03-05-17.05.43.111111'')';
+execute dateTimePS using 'values(2, ''20030422190200'')';
+
+values( date(date(1)), date(timestamp('2003-03-05-17.05.43.111111')));
+
+drop table t;
+create table t( s varchar(32), ts timestamp, expected timestamp);
+commit;
+
+insert into t(ts) values( timestamp('2003-03-05-17.05.43.111111'));
+select date(ts) from t;
+rollback;
+
+-- Test special unary timestamp function rules: yyyyxxddhhmmss
+insert into t values('20000228235959', timestamp('20000228235959'), '2000-02-28-23.59.59'),
+                    ('20000229000000', timestamp('20000229000000'), '2000-02-29-00.00.00');
+select s from t where ts &lt;&gt; expected or timestamp(s) &lt;&gt; expected
or timestamp(ts) &lt;&gt; expected;
+rollback;
+
+-- invalid
+values( timestamp('2000 1 1 0 0 0'));
+values( timestamp('aaaaaaaaaaaaaa'));
+
+insert into t(s) values('2000 1 1 0 0 0');
+select timestamp(s) from t;
+rollback;
+
+insert into t(s) values('aaaaaaaaaaaaaa');
+select timestamp(s) from t;
+rollback;
Index: java/testing/org/apache/derbyTesting/functionTests/master/datetime.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/datetime.out	(revision 164436)
+++ java/testing/org/apache/derbyTesting/functionTests/master/datetime.out	(working copy)
@@ -148,21 +148,21 @@
 ij&gt; -- show that overflow and underflow are not allowed
 -- (SQL92 would have these report errors)
 values( date('0000-01-01'));
-ERROR 22007: The string representation of a datetime value is out of range.
+ERROR 22008: '0000-01-01' is an invalid argument to the date function.
 ij&gt; values( date('2000-00-01'));
-ERROR 22007: The string representation of a datetime value is out of range.
+ERROR 22008: '2000-00-01' is an invalid argument to the date function.
 ij&gt; values( date('2000-01-00'));
-ERROR 22007: The string representation of a datetime value is out of range.
+ERROR 22008: '2000-01-00' is an invalid argument to the date function.
 ij&gt; values( date('10000-01-01'));
-ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
+ERROR 22008: '10000-01-01' is an invalid argument to the date function.
 ij&gt; values( date('2000-13-01'));
-ERROR 22007: The string representation of a datetime value is out of range.
+ERROR 22008: '2000-13-01' is an invalid argument to the date function.
 ij&gt; values( date('2000-01-32'));
-ERROR 22007: The string representation of a datetime value is out of range.
+ERROR 22008: '2000-01-32' is an invalid argument to the date function.
 ij&gt; values( date('1900-02-29'));
-ERROR 22007: The string representation of a datetime value is out of range.
+ERROR 22008: '1900-02-29' is an invalid argument to the date function.
 ij&gt; values( date('2001-02-29'));
-ERROR 22007: The string representation of a datetime value is out of range.
+ERROR 22008: '2001-02-29' is an invalid argument to the date function.
 ij&gt; values( time('25.00.00'));
 ERROR 22007: The string representation of a datetime value is out of range.
 ij&gt; values( time('24.00.01'));
@@ -173,15 +173,15 @@
 ERROR 22007: The string representation of a datetime value is out of range.
 ij&gt; -- show garbage in == errors out
 select date( 'xxxx') from t where p is null;
-ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
+ERROR 22008: 'xxxx' is an invalid argument to the date function.
 ij&gt; select time( '') from t where p is null;
 ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
 ij&gt; select timestamp( 'is there anything here?' )from t where p is null;
-ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
+ERROR 22008: 'is there anything here?' is an invalid argument to the timestamp function.
 ij&gt; select timestamp( '1992-01- there anything here?' )from t where p is null;
-ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
+ERROR 22008: '1992-01- there anything here?' is an invalid argument to the timestamp function.
 ij&gt; select timestamp( '--::' )from t where p is null;
-ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
+ERROR 22008: '--::' is an invalid argument to the timestamp function.
 ij&gt; select time('::::') from t where p is null;
 ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
 ij&gt; -- show is not null at work
@@ -957,7 +957,16 @@
 1 row inserted/updated/deleted
 ij&gt; insert into t values ('17:05:11  ');
 1 row inserted/updated/deleted
-ij&gt; -- 7 rows
+ij&gt; -- seconds can be omitted
+insert into t values ('1:01');
+1 row inserted/updated/deleted
+ij&gt; insert into t values ('1:02 ');
+1 row inserted/updated/deleted
+ij&gt; insert into t values ('2.01');
+1 row inserted/updated/deleted
+ij&gt; insert into t values ('2.02 ');
+1 row inserted/updated/deleted
+ij&gt; -- 11 rows
 select * from t;
 T       
 --------
@@ -968,8 +977,12 @@
 00:00:00
 17:05:11
 17:05:11
+01:01:00
+01:02:00
+02:01:00
+02:02:00
 ij&gt; delete from t;
-7 rows inserted/updated/deleted
+11 rows inserted/updated/deleted
 ij&gt; -- end value tests...
 insert into t values ('24.60.60');
 ERROR 22007: The string representation of a datetime value is out of range.
@@ -1027,7 +1040,174 @@
 ----------
 2004-04-15
 ij&gt; values date('2004-04-15-16.15.32.387 zz');
-ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
+ERROR 22008: '2004-04-15-16.15.32.387 zz' is an invalid argument to the date function.
 ij&gt; values date('2004-04-15-16.15.32.y');
-ERROR 22007: The syntax of the string representation of a datetime value is incorrect.
+ERROR 22008: '2004-04-15-16.15.32.y' is an invalid argument to the date function.
+ij&gt; values time('13:59');
+1       
+--------
+13:59:00
+ij&gt; values time('1:00');
+1       
+--------
+01:00:00
+ij&gt; -- Test unary date and datetime functions. Test with both constant and variable
arguments.
+autocommit off;
+ij&gt; -- test date(integer)
+create table t( i int, d date);
+0 rows inserted/updated/deleted
+ij&gt; commit;
+ij&gt; insert into t values( 1, date(1)),(10, date(10.1)),(365,date(365.1e0)),(366,date(366)),(789,date(789)),(790,date(790)),(791,date(791));
+7 rows inserted/updated/deleted
+ij&gt; -- should fail
+insert into t values( 0, date(0));
+ERROR 22008: '0' is an invalid argument to the date function.
+ij&gt; insert into t values( -1, date(-1));
+ERROR 22008: '-1' is an invalid argument to the date function.
+ij&gt; insert into t values( 3652060, date( 3652060));
+ERROR 22008: '3652060' is an invalid argument to the date function.
+ij&gt; select i,d,date(i),date(d) from t order by i;
+I          |D         |3         |4         
+--------------------------------------------
+1          |1970-01-01|1970-01-01|1970-01-01
+10         |1970-01-10|1970-01-10|1970-01-10
+365        |1970-12-31|1970-12-31|1970-12-31
+366        |1971-01-01|1971-01-01|1971-01-01
+789        |1972-02-28|1972-02-28|1972-02-28
+790        |1972-02-29|1972-02-29|1972-02-29
+791        |1972-03-01|1972-03-01|1972-03-01
+ij&gt; rollback;
+ij&gt; insert into t(i) values( 0);
+1 row inserted/updated/deleted
+ij&gt; select date(i) from t;
+1         
+----------
+ERROR 22008: '0' is an invalid argument to the date function.
+ij&gt; rollback;
+ij&gt; insert into t(i) values( -1);
+1 row inserted/updated/deleted
+ij&gt; select date(i) from t;
+1         
+----------
+ERROR 22008: '-1' is an invalid argument to the date function.
+ij&gt; rollback;
+ij&gt; insert into t(i) values( 3652060);
+1 row inserted/updated/deleted
+ij&gt; select date(i) from t;
+1         
+----------
+ERROR 22008: '3652060' is an invalid argument to the date function.
+ij&gt; rollback;
+ij&gt; drop table t;
+0 rows inserted/updated/deleted
+ij&gt; create table t( s varchar(32), d date);
+0 rows inserted/updated/deleted
+ij&gt; commit;
+ij&gt; insert into t values('1900060', date('1900060')),
+                    ('1904060', date('1904060')),
+                    ('1904366', date('1904366')),
+                    ('2000060', date('2000060')),
+                    ('2001060', date('2001060')),
+                    ('2001365', date('2001365'));
+6 rows inserted/updated/deleted
+ij&gt; select s,d,date(s) from t order by s;
+S                               |D         |3         
+------------------------------------------------------
+1900060                         |1900-03-01|1900-03-01
+1904060                         |1904-02-29|1904-02-29
+1904366                         |1904-12-31|1904-12-31
+2000060                         |2000-02-29|2000-02-29
+2001060                         |2001-03-01|2001-03-01
+2001365                         |2001-12-31|2001-12-31
+ij&gt; rollback;
+ij&gt; -- failure cases
+values( date('2001000'));
+ERROR 22008: '2001000' is an invalid argument to the date function.
+ij&gt; values( date('2001366'));
+ERROR 22008: '2001366' is an invalid argument to the date function.
+ij&gt; values( date('2000367'));
+ERROR 22008: '2000367' is an invalid argument to the date function.
+ij&gt; values( date('xxxxxxx'));
+ERROR 22008: 'xxxxxxx' is an invalid argument to the date function.
+ij&gt; insert into t(s) values( '2001000');
+1 row inserted/updated/deleted
+ij&gt; select date(s) from t;
+1         
+----------
+ERROR 22008: '2001000' is an invalid argument to the date function.
+ij&gt; rollback;
+ij&gt; insert into t(s) values( '2001366');
+1 row inserted/updated/deleted
+ij&gt; select date(s) from t;
+1         
+----------
+ERROR 22008: '2001366' is an invalid argument to the date function.
+ij&gt; rollback;
+ij&gt; insert into t(s) values( '2000367');
+1 row inserted/updated/deleted
+ij&gt; select date(s) from t;
+1         
+----------
+ERROR 22008: '2000367' is an invalid argument to the date function.
+ij&gt; rollback;
+ij&gt; insert into t(s) values( 'xxxxxxx');
+1 row inserted/updated/deleted
+ij&gt; select date(s) from t;
+1         
+----------
+ERROR 22008: 'xxxxxxx' is an invalid argument to the date function.
+ij&gt; rollback;
+ij&gt; -- test parameter
+prepare dateTimePS as 'values( date(cast(? as integer)),timestamp(cast(? as varchar(32))))';
+ij&gt; execute dateTimePS using 'values(cast(1 as integer), ''2003-03-05-17.05.43.111111'')';
+1         |2                         
+-------------------------------------
+1970-01-01|2003-03-05-17.05.43.111111
+ij&gt; execute dateTimePS using 'values(2, ''20030422190200'')';
+1         |2                         
+-------------------------------------
+1970-01-02|xxxxxxFILTERED-TIMESTAMPxxxxx
+ij&gt; values( date(date(1)), date(timestamp('2003-03-05-17.05.43.111111')));
+1         |2         
+---------------------
+1970-01-01|2003-03-05
+ij&gt; drop table t;
+0 rows inserted/updated/deleted
+ij&gt; create table t( s varchar(32), ts timestamp, expected timestamp);
+0 rows inserted/updated/deleted
+ij&gt; commit;
+ij&gt; insert into t(ts) values( timestamp('2003-03-05-17.05.43.111111'));
+1 row inserted/updated/deleted
+ij&gt; select date(ts) from t;
+1         
+----------
+2003-03-05
+ij&gt; rollback;
+ij&gt; -- Test special unary timestamp function rules: yyyyxxddhhmmss
+insert into t values('20000228235959', timestamp('20000228235959'), '2000-02-28-23.59.59'),
+                    ('20000229000000', timestamp('20000229000000'), '2000-02-29-00.00.00');
+2 rows inserted/updated/deleted
+ij&gt; select s from t where ts &lt;&gt; expected or timestamp(s) &lt;&gt;
expected or timestamp(ts) &lt;&gt; expected;
+S                               
+--------------------------------
+ij&gt; rollback;
+ij&gt; -- invalid
+values( timestamp('2000 1 1 0 0 0'));
+ERROR 22008: '2000 1 1 0 0 0' is an invalid argument to the timestamp function.
+ij&gt; values( timestamp('aaaaaaaaaaaaaa'));
+ERROR 22008: 'aaaaaaaaaaaaaa' is an invalid argument to the timestamp function.
+ij&gt; insert into t(s) values('2000 1 1 0 0 0');
+1 row inserted/updated/deleted
+ij&gt; select timestamp(s) from t;
+1                         
+--------------------------
+ERROR 22008: '2000 1 1 0 0 0' is an invalid argument to the timestamp function.
+ij&gt; rollback;
+ij&gt; insert into t(s) values('aaaaaaaaaaaaaa');
+1 row inserted/updated/deleted
+ij&gt; select timestamp(s) from t;
+1                         
+--------------------------
+ERROR 22008: 'aaaaaaaaaaaaaa' is an invalid argument to the timestamp function.
+ij&gt; rollback;
 ij&gt; 
  </pre>
</blockquote>
</body>
</html>


Mime
View raw message