如何在HQL中实现日期相减？含毫秒及日期差值需求

Absolutely, you can perform date subtraction in HQL—including operations down to milliseconds—without relying on non-standard extensions like the minus method you found. The QuerySyntaxException you’re seeing is likely because that method isn’t part of standard HQL or your JPA provider’s supported syntax.

Here’s how to adjust your query to work with standard HQL (using Hibernate, which your entity aliases suggest you’re using):

Correct HQL Query for Your Use Case #

h.createDate < CASE 
    WHEN h.timeout IS NOT NULL THEN dateadd('millisecond', -h.timeout, current_timestamp()) 
    ELSE :date 
END

Breakdown of the Solution: #

• dateadd Function: This is a standard HQL function (supported in Hibernate 5+ and JPA 2.2+) that lets you add or subtract time intervals from a date. The syntax is dateadd(unit, amount, date):
  • 'millisecond': Specifies we’re working with millisecond intervals. Swap this for 'second', 'minute', etc., if your timeout uses a different unit.
  • -h.timeout: Using a negative value here effectively subtracts the timeout value (in milliseconds) from the current timestamp.
  • current_timestamp(): Returns the current database timestamp (equivalent to SQL’s CURRENT_TIMESTAMP).
• CASE Statement: Your original conditional logic stays fully intact—we only adjust the date subtraction part to use valid HQL syntax.

If You’re Using an Older Hibernate Version #

If dateadd isn’t available (pre-Hibernate 5), you can use the function() call to invoke your database’s native date functions for cross-compatibility:

• MySQL: Use date_sub

  h.createDate < CASE 
      WHEN h.timeout IS NOT NULL THEN function('date_sub', current_timestamp(), function('interval', h.timeout, 'millisecond')) 
      ELSE :date 
  END
  

• PostgreSQL: Cast the timeout to an interval

  h.createDate < CASE 
      WHEN h.timeout IS NOT NULL THEN current_timestamp() - function('cast', concat(h.timeout, ' milliseconds'), 'interval') 
      ELSE :date 
  END
  

This approach avoids non-standard syntax, resolves your QuerySyntaxException, and meets your requirement to subtract milliseconds (or other time units) directly in HQL.

内容的提问来源于stack exchange，提问作者Kirill