TimeStamp的JPQL avg函数(JPQL avg function for TimeStamp)

TimeStamp的JPQL avg函数(JPQL avg function for TimeStamp)

我需要一个JPQL查询，它应该计算两个日期之间的平均天数。 我写了这样的查询

select avg(ps.begins-ps.ends) from PackStatus ps where ps.status.code = 'serviceExec' and ps.gossrvcDocPackage.serviceId = 'Usl_3641042_RSOC' and ps.begins between :startDate and :endDate and ps.ends between :startDate and :endDate

但系统抛出异常“org.hibernate.exception.DataException：无法执行查询”如果我放了一些其他字段，例如avg（ps.id），它可以正常工作。 在数据库中，ps.ends和ps.begins存储为timestapm。 我如何计算两个日期之间的平均天数？ 谢谢。

I need a JPQL query which should count average days between two dates. I wrote the query like this

select avg(ps.begins-ps.ends) from PackStatus ps where ps.status.code = 'serviceExec' and ps.gossrvcDocPackage.serviceId = 'Usl_3641042_RSOC' and ps.begins between :startDate and :endDate and ps.ends between :startDate and :endDate

But the system throws exception "org.hibernate.exception.DataException: could not execute query" If I put some other field, e.g avg(ps.id) it works fine. In database ps.ends and ps.begins are stored as timestapm. How can I count avg days between two dates? Thanks.

最满意答案

找到日期之间的平均差异的正确方法是

select avg(day(ps.ends)-day(ps.begins)) from PackStatus ps where ps.status.code = 'serviceExec' and ps.gossrvcDocPackage.serviceId = 'Usl_3641042_RSOC' and ps.begins between :startDate and :endDate and ps.ends between :startDate and :endDate

你也可以找到小时，分钟等的差异。只需在小时（）或其他措施上替换day（）。

The right way to find avg difference between dates in days is

select avg(day(ps.ends)-day(ps.begins)) from PackStatus ps where ps.status.code = 'serviceExec' and ps.gossrvcDocPackage.serviceId = 'Usl_3641042_RSOC' and ps.begins between :startDate and :endDate and ps.ends between :startDate and :endDate

Also you can find difference in hours,minutes, etc. Just replace day() on hour() or other measure.