db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jeff Levitt <de...@mylevita.com>
Subject Re: [PATCH] Timestamp Arithmetic
Date Fri, 13 May 2005 17:58:22 GMT
If this gets committed, I'd like to place these new
functions in the docs.  Would they go in the built-in
functions section of the Reference Manual?


--- Jack Klebanoff <klebanoff-derby@sbcglobal.net>
wrote:

> The attached patch adds support for the JDBC
> TIMESTAMPADD and 
> TIMESTAMPDIFF escape functions. JDBC escape
> functions may be embedded in 
> SQL. JDBC drivers convert them to the syntax used by
> the underlying 
> database. Derby handles JDBC escapes directly in its
> parser.
> 
> 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)
> 
> I have run the patch against the derbylang test
> suite.
> 
> The svn status is:
> 
> M      tools\jar\DBMSnodes.properties
> M     
>
java\engine\org\apache\derby\impl\sql\compile\NodeFactoryImpl.java
> M     
>
java\engine\org\apache\derby\impl\sql\compile\C_NodeNames.java
> A      
>
java\engine\org\apache\derby\impl\sql\compile\TimestampArithmeticFnNode.java
> M     
>
java\engine\org\apache\derby\impl\sql\compile\sqlgrammar.jj
> M     
>
java\engine\org\apache\derby\iapi\sql\compile\C_NodeTypes.java
> M     
>
java\engine\org\apache\derby\iapi\types\DateTimeDataValue.java
> M     
> java\engine\org\apache\derby\iapi\types\SQLDate.java
> M     
> java\engine\org\apache\derby\iapi\types\SQLTime.java
> M     
>
java\engine\org\apache\derby\iapi\types\SQLTimestamp.java
> M     
>
java\engine\org\apache\derby\iapi\reference\SQLState.java
> M     
>
java\engine\org\apache\derby\loc\messages_en.properties
> A      
>
java\testing\org\apache\derbyTesting\functionTests\tests\lang\timestampArith.java
> A      
>
java\testing\org\apache\derbyTesting\functionTests\master\timestampArith.out
> M      
>
java\testing\org\apache\derbyTesting\functionTests\suites\derbylang.runall
> 
> 
> Jack Klebanoff
> > Index: tools/jar/DBMSnodes.properties
>
===================================================================
> --- tools/jar/DBMSnodes.properties	(revision 169901)
> +++ tools/jar/DBMSnodes.properties	(working copy)
> @@ -114,3 +114,4 @@
> 
>
derby.module.cloudscapenodes.ge=org.apache.derby.impl.sql.compile.SavepointNode
> 
>
derby.module.cloudscapenodes.gf=org.apache.derby.impl.sql.compile.IntersectOrExceptNode
> 
>
derby.module.cloudscapenodes.gg=org.apache.derby.impl.sql.compile.UnaryDateTimestampOperatorNode
>
+derby.module.cloudscapenodes.gh=org.apache.derby.impl.sql.compile.TimestampArithmeticFnNode
> Index:
>
java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
>
===================================================================
> ---
>
java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
> (revision 169901)
> +++
>
java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
> (working copy)
> @@ -563,8 +563,12 @@
>  		  case C_NodeTypes.DB2_LENGTH_OPERATOR_NODE:
>              return
> C_NodeNames.DB2_LENGTH_OPERATOR_NODE_NAME;
>  
> +		  case C_NodeTypes.TIMESTAMP_ADD_FN_NODE:
> +		  case C_NodeTypes.TIMESTAMP_DIFF_FN_NODE:
> +              return
> C_NodeNames.TIMESTAMP_ARITHMETIC_FN_NODE_NAME;
> +
>  		  // WARNING: WHEN ADDING NODE TYPES HERE, YOU
> MUST ALSO ADD
> -		  // THEM TO
>
$WS/tools/release/config/dbms/cloudscapenodes.properties
> +		  // THEM TO tools/jar/DBMSnodes.properties
>  
>  		  default:
>  			throw
>
StandardException.newException(SQLState.NOT_IMPLEMENTED);
> Index:
>
java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
>
===================================================================
> ---
>
java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
> (revision 169901)
> +++
>
java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
> (working copy)
> @@ -248,6 +248,8 @@
>  
>  	static final String TIMESTAMP_OPERATOR_NODE_NAME =
>
"org.apache.derby.impl.sql.compile.TimestampOperatorNode";
>  
> +	static final String
> TIMESTAMP_ARITHMETIC_FN_NODE_NAME =
>
"org.apache.derby.impl.sql.compile.TimestampArithmeticFnNode";
> +
>  	static final String
> UNARY_ARITHMETIC_OPERATOR_NODE_NAME =
>
"org.apache.derby.impl.sql.compile.UnaryArithmeticOperatorNode";
>  
>  	static final String
> UNARY_DATE_TIMESTAMP_OPERATOR_NODE_NAME
> Index:
>
java/engine/org/apache/derby/impl/sql/compile/TimestampArithmeticFnNode.java
>
===================================================================
> ---
>
java/engine/org/apache/derby/impl/sql/compile/TimestampArithmeticFnNode.java
> (revision 0)
> +++
>
java/engine/org/apache/derby/impl/sql/compile/TimestampArithmeticFnNode.java
> (revision 0)
> @@ -0,0 +1,371 @@
> +/*
> +
> +   Derby - Class
>
org.apache.derby.impl.sql.compile.TimestampArithmeticFnNode
> +
> +   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
> +
> +      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.derby.impl.sql.compile;
> +
> +import
> org.apache.derby.iapi.error.StandardException;
> +
> +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.compiler.LocalField;
> +import
>
org.apache.derby.iapi.services.compiler.MethodBuilder;
> +import
> org.apache.derby.iapi.services.sanity.SanityManager;
> +
> +import
> org.apache.derby.iapi.sql.compile.C_NodeTypes;
> +import org.apache.derby.iapi.sql.compile.Visitable;
> +import org.apache.derby.iapi.sql.compile.Visitor;
> +
> +import
> org.apache.derby.iapi.types.DataTypeDescriptor;
> +import
> org.apache.derby.iapi.types.DataValueFactory;
> +import org.apache.derby.iapi.types.TypeId;
> +
> +import org.apache.derby.iapi.util.JBitSet;
> +import org.apache.derby.iapi.util.ReuseFactory;
> +
> +import java.sql.Types;
> +
> +import java.util.Vector;
> +import java.lang.reflect.Modifier;
> +
> +/**
> + * This class implements the JDBC TIMESTAMPADD and
> TIMESTAMPDIFF escape functions.
> + */
> +
> +public class TimestampArithmeticFnNode extends
> ValueNode
> +{
> +    // The functions
> +    public static final int ADD_FN = 1;
> +    public static final int DIFF_FN = 2;
> +
> +    private static final String dateTimeType =
> ClassName.DateTimeDataValue;
> +
> +    private int fnNumber;
> +    private int intervalType;
> +    /**
> +     * The second and third arguments, those beyond
> the interval type.
> +     */
> +    private ValueNode[] args23 = new ValueNode[2];
> +    private String methodName;
> +    private String arg2Type;
> +    private String returnType;
> +    
> +    public void init( Object intervalType,
> +                      Object arg2,
> +                      Object arg3)
> +    {
> +		switch(getNodeType())
> +		{
> +        case C_NodeTypes.TIMESTAMP_ADD_FN_NODE:
> +            fnNumber = ADD_FN;
> +            arg2Type = ClassName.NumberDataValue;
> +            methodName = "timestampAdd";
> +            returnType =
> ClassName.DateTimeDataValue;
> +            break;
> +            
> +        case C_NodeTypes.TIMESTAMP_DIFF_FN_NODE:
> +            fnNumber = DIFF_FN;
> +            arg2Type = ClassName.DateTimeDataValue;
> +            methodName = "timestampDiff";
> +            returnType = ClassName.NumberDataValue;
> +            break;
> +
> +        default:
> +            if (SanityManager.DEBUG)
> +            {
> +                SanityManager.THROWASSERT("init for
> TimestampArithmeticFnNode called with wrong nodeType
> = "
> +                                          +
> getNodeType());
> +            }
> +            break;
> +		}
> +            
> +        this.intervalType = ((Integer)
> intervalType).intValue();
> +        args23[0] = (ValueNode) arg2;
> +        args23[1] = (ValueNode) arg3;
> 
=== message truncated ===


Mime
View raw message