Derby SQL时间戳算法和JDBC转义语法 [英] Derby SQL Timestamp Arithmetic and JDBC escape syntax

问题描述

我的表中有两列，即Created(TIMESTAMP)和Lifetime(BIGINT).生命周期表示为秒.我想将生命周期列添加到时间戳列，并将该结果与另一个日期进行比较.

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.

我参考了德比手册，并在"fn关键字的JDBC转义语法"部分:

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)

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

好的，所以我尝试了:

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:

异常描述:语法错误解析查询[SELECT i.messageId来自ServerMessageEntity，我在{fn TIMESTAMPADD(SQL_TSI_SECOND，i.lifetime，i.created)}>:now]， 意外的字符[{].

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!

其他信息:我正在将Oracle TopLink 12c(12.1.2)用于ORM和Derby 10.10.2.0

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

推荐答案

事实证明，我的问题是使用"@NamedQuery"存储查询.我通过使用"createNativeQuery"方法进行了实验，并能够获得所需的结果

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);

这篇关于Derby SQL时间戳算法和JDBC转义语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！