如何在Hibernate SQL中从LocalTime中减去Duration

Question

我在Hibernate的AppointmentEntity实体中有一个名为mobileAdditionalDuration的Duration字段。在我的AppointmentRepository接口中，我试图编写一个SQL查询，从startTime和endTime字段中减去此持续时间，其类型为LocalTime。

下面是我写的查询:

@Query(
value = """
SELECT EXISTS (
SELECT 
a.date, 
SUBTIME(a.startTime, a.mobileAdditionalDuration) as startTime1, 
SUBTIME(a.endTime, a.mobileAdditionalDuration) as endTime1 
FROM appointment a 
WHERE (a.date = ?1) AND ((startTime1 < ?2 AND endTime1 > ?2) OR (startTime1 < ?3 AND endTime1 > ?3))
)
"""
)
fun checkIfAppointmentAlreadyExistAtThisTime(date: LocalDate, start: LocalTime, end: LocalTime): Boolean

然而，当我运行查询时，我得到异常Could not interpret path expression 'aStartTime'。有人能帮我纠正我的查询，以达到预期的结果吗?

Answer 1

HQL-运算符用于从LocalTime中减去Duration，如下所示:

https://docs.jboss.org/hibernate/orm/6.2/userguide/html_single/Hibernate_User_Guide.html hql-Datetime-arithmetic