db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Suresh Thalamati (JIRA)" <derby-...@db.apache.org>
Subject [jira] Commented: (DERBY-355) Document TIMESTAMPADD and TIMESTAMPDIFF functions
Date Thu, 16 Jun 2005 19:40:47 GMT
    [ 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


Mime
View raw message