在 HQL 中执行日期/时间数学? [英] Performing Date/Time Math In HQL?

问题描述

我正在研究如何在 HQL 查询中执行日期/时间数学运算.具体来说，如何从 current_timestamp() 函数的结果中添加或减去 (x) 时间量?或者我是否必须为此投入 SQL 并希望正在运行的任何数据库都支持它?

I'm looking how to perform date/time math within an HQL query. Specifically, how do I add or subtract (x) amount of time from the result of the current_timestamp() function? Or do I have to drop into SQL for this and hope that whatever database is being run supports it?

HQL 查询示例:

FROM RandomThing
WHERE randomTime IS NOT NULL AND
      randomTime >= current_timestamp() AND
      randomTime <= (current_timestamp() + :timeToAdd)

我可以将 :timeToSubtract 参数定义为任何特定单位，但任何大于小时的都是不可取的，而秒则是最可取的.

I can define the :timeToSubtract parameter to be any particular unit, though anything bigger than hours would be undesirable, and seconds would be most desirable.

澄清:我意识到这可以在查询之外轻松完成.但出于哲学原因，假设使用数据库服务器的时间而不是查询系统的时间很重要.实际示例:我正在查询最后 (x) 时间内所有条目的自动时间戳.由于时间戳是由数据库系统生成的，因此使用数据库的当前时间也很重要.

CLARIFICATION: I realize this can be easily done outside of the query. But for philosophical reasons, let's say it's important to use the database server's time, rather than the querying system's time. Practical example: I'm querying an automatic timestamp for all entries made within the last (x) amount of time. Since the timestamp is made by the database system, it is important to also use the database's current time.

推荐答案

如果你需要数据库服务器的时间，你可以先做一个简单的hql查询来获取时间戳，然后在java中计算maxTimestamp并传递获取的时间戳和计算的 maxTimeStamp 到一个查询，如 ccclark 之一.

If you need the database server's time, you could first do a simple hql query to get the timestamp and then calculate the maxTimestamp in java and pass the fetched timestamp and the calculated maxTimeStamp to a query like the one of ccclark.

这篇关于在 HQL 中执行日期/时间数学?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！