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: [jira] Commented: (DERBY-355) Document TIMESTAMPADD and TIMESTAMPDIFF functions
Date Thu, 16 Jun 2005 19:45:53 GMT
--- "Suresh Thalamati (JIRA)"
<derby-dev@db.apache.org> wrote:

> description of  TIMESTAMPADD/TIMESTAMPDIFF seems to
> indicate that  column name 
> should not be put in these escape function because 
> it will use index, I am not sure that is right ?
> my understanding   is if you put the column name
> inside the function ,  Index will NOT be used. 
>  
> TIMESTAMPADD page:  (in the end ..)
> Note that you should not put a datetime column
> inside a timestamp arithmetic function in WHERE
> clauses because the optimizer will use any index on
> the column.
> 
> TIMESTAMPDIFF section: (in the end .,)
> Note that you should not put a datetime column
> inside a timestamp arithmetic function in WHERE
> clauses because the optimizer will use any index on
> the column.
> 
> 
> jack wrote in his patch submission:
> 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)
> 

Thanks for the feedback Suresh,

I've already tweaked this patch about 5 times, so this
time I want to make sure I get it right before I
submit a new patch.  How would you suggest I rewrite
it so that it is accurate?

Mime
View raw message