[ http://issues.apache.org/jira/browse/DERBY-81?page=comments#action_66203 ] Jack Klebanoff commented on DERBY-81: -------------------------------------
As of revision 178061 Derby supports the ODBC/JCBC TIMESTAMPADD and TIMESTAMPDIFF escape functions. So the query in the request can be written as SELECT * FROM log WHERE modified_time > TIMESTAMPADD( SQL_TSI_MONTH, 1, CURRENT_TIMESTAMP) Unfortunately, the major SQL databases all seem to implement datetime arithmetic in different and incompatible ways. The ODBC/JDBC escape functions, though somewhat awkward, are the closest thing to a common denominator here. > Datetime datatypes should allow arithmetic operations on them. > -------------------------------------------------------------- > > Key: DERBY-81 > URL: http://issues.apache.org/jira/browse/DERBY-81 > Project: Derby > Type: New Feature > Components: SQL > Versions: 10.0.2.0 > Environment: Generic > Reporter: Satheesh Bandaram > Assignee: Jack Klebanoff > > Datetime datatypes in Derby are date, time and timestamp. Currently Derby > doesn't allow any arithmetic operations on them, limiting their use. It would > be great to allow statements like: > SELECT * FROM log > WHERE modified_time > CURRENT_TIMESTAMP - 1 MONTH > Commercial databases like Oracle, DB2 and mySQL also have this feature. SQL > standard allows these operations along with INTERVAL types. Since Derby > doesn't have INTERVAL datatypes, I would like to propose datetime arithmetic > to Derby without INTERVALs. > It should be possible to add, subtract datetime datatypes with a constant > like '1 MONTH' (called a duration or INTERVAL in SQL standard). It should > also be possible to subtract datetime types where the semantics are allowed > according to SQL standard 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
