Derby SQL Timestamp Arithmetic and JDBC escape syn

问题:

I have two columns in my table, Created(TIMESTAMP) and lifetime(BIGINT). Lifetime is represented as seconds. I would like to add the lifetime column to the timestamp column and compare that result to another date.

I referenced the derby manual and found support for TIMESTAMPADD in the "JDBC escape syntax for fn keyword" section:

{fn functionCall} TIMESTAMPADD( interval, integerExpression, timestampExpression )

Okay so I tried:

SELECT i.messageId FROM ServerMessageEntity i
WHERE {fn TIMESTAMPADD(SQL_TSI_SECOND, i.lifetime, i.created)} > :now

*:now is a parameter I pass in by:

query.setParameter

Unfortunitly I get the following exception:

Exception Description: Syntax error parsing the query [SELECT i.messageId FROM ServerMessageEntity i WHERE {fn TIMESTAMPADD(SQL_TSI_SECOND, i.lifetime, i.created)} > :now], unexpected char [{].

It seems like the solution would be to add the seconds to the created timestamp and have the result be another timestamp. Any help or syntax for how to do this would be appreciated. Thanks!

Other information: I am using Oracle TopLink 12c (12.1.2) for ORM and Derby 10.10.2.0

回答1:

It turns out that my problem was using "@NamedQuery" to store the query. I experimented by using the "createNativeQuery" method and was able to get the result I wanted

String sql = "SELECT MESSAGEID FROM \"TABLE\".SERVERMESSAGES WHERE {fn TIMESTAMPADD(SQL_TSI_SECOND, LIFETIME, CREATED)} < ?"
Query query = entityManager.createNativeQuery(sql);