2

I want to calculate the average of the time difference of two timestamp fields of an entity, such that; entity has start_date and finish_date attibutes. In SQL, it is like the following:

SELECT SUM(FINISH_TIME - START_TIME) / COUNT(*) FROM PROCESS_EXECUTION_LOG WHERE FINISH_TIME IS NOT NULL

When I try to run this as native sql in hibernate:

Session session = sessionFactory.openSession();
SQLQuery sqlQuery = session
            .createSQLQuery("SELECT SUM(FINISH_TIME - START_TIME) / COUNT(*) FROM PROCESS_EXECUTION_LOG WHERE FINISH_TIME IS NOT NULL");
sqlQuery.list();

it has thrown an exeption:

   org.hibernate.MappingException: No Dialect mapping for JDBC type: 1111
    at org.hibernate.dialect.TypeNames.get(TypeNames.java:76)
    at org.hibernate.dialect.TypeNames.get(TypeNames.java:99)
    at org.hibernate.dialect.Dialect.getHibernateTypeName(Dialect.java:579)
    at org.hibernate.loader.custom.CustomLoader$Metadata.getHibernateType(CustomLoader.java:696)
    at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.performDiscovery(CustomLoader.java:600)
    at org.hibernate.loader.custom.CustomLoader.autoDiscoverTypes(CustomLoader.java:616)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2039)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1832)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
    at org.hibernate.loader.Loader.doQuery(Loader.java:899)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
    at org.hibernate.loader.Loader.doList(Loader.java:2516)
    at org.hibernate.loader.Loader.doList(Loader.java:2502)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2332)
    at org.hibernate.loader.Loader.list(Loader.java:2327)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1783)
    at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:231)
    at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:157)

It may be very trivial but I couldn't find the solution. If there is a solution using Hibernate API(Restrictions, Projections, etc) it will be very useful.

EDITED: I've just realized that postgresql returns INTERVAL datatype which is unknown for JDBC. I think that the only solution is rewrite this SQL that returns the result in milliseconds (as number).But, I don't know how?

st.
  • 166
  • 6
  • 24

1 Answers1

1

Do like this

String sql = "SELECT SUM(FINISH_TIME - START_TIME) / COUNT(*) FROM PROCESS_EXECUTION_LOG WHERE FINISH_TIME IS NOT NULL";

Session session = sessionFactory.openSession();
SQLQuery sqlQuery = session
            .createSQLQuery(sql);
Long value = (Long)sqlQuery.uniqueResult();
Prabhakaran Ramaswamy
  • 25,706
  • 10
  • 57
  • 64