[
http://issues.apache.org/jira/browse/DERBY-355?page=comments#action_12313860 ]
Suresh Thalamati commented on DERBY-355:
----------------------------------------
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)
> Document TIMESTAMPADD and TIMESTAMPDIFF functions
> -------------------------------------------------
>
> Key: DERBY-355
> URL: http://issues.apache.org/jira/browse/DERBY-355
> Project: Derby
> Type: Sub-task
> Components: Documentation
> Environment: all
> Reporter: Jeff Levitt
> Assignee: Jeff Levitt
> Priority: Minor
> Fix For: 10.1.0.0
> Attachments: derby355finaldraft3.zip
>
> Derby 81 (parent task to this issue) needs to be documented. TIMESTAMPADD
> and TIMESTAMPDIFF need to be included in the reference manual with the
> built-in functions. I created a patch for this which I will attach to this
> issue for review. I used
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbctime__date__and_interval_functions.asp
> as my specification.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
