Search code examples
javapostgresqlhibernatejpahql

How can I query the average difference of dates in days using HQL (Hibernate)?

I need to retrieve from my PostgreSQL database, which has been mapped to Java using Hibernate, the average difference of dates (start and end, as you may say) of the records.

I wrote a native PostgreSQL query which works fine:

SELECT avg(date_part('days', age(datasaida, dataentrada))) as avg_days
  FROM processo.processo
  WHERE processo.codsituacao = '14'
  AND processo.dataEntrada >=  now() - interval '30 days';

The problem is that I can't figure out how to translate this query to HQL (Hibernate SQL) because of the avg(date_part('days', age(datasaida, dataentrada))) part.

I need the information to be shown in the front-end, which I am building with JSF Primefaces.

P.S.: dataEntrada - means startDate (kind of). dataSaida means endDate (kind of)

Solution

  • You could try

    avg(DAY(function('age', datasaida, dataentrada)))

or

avg(DAY(datasaid - dataentrada))

    DAY is standard JPA function: http://www.objectdb.com/java/jpa/query/jpql/date

    And with function('age', datasaida, dataentrada) you call a database specific function from JPA