I stuck in two problems with hibernate queries today, I hope somebody has an answer to them!
I want to get the average (and standard derivation) of time between two process start events. For this I want to query the ProcessInstanceCreateLog.
First problem: I need to calculate the time in between. The "clean" way would be like this I think:
select (select min(pl2.date) - pl.date from org.jbpm.graph.log.ProcessInstanceCreateLog pl2 where pl2.date > pl.date) from org.jbpm.graph.log.ProcessInstanceCreateLog pl where pl.token.processInstance.processDefinition = :processDefinition and exists (select pl2.date from org.jbpm.graph.log.ProcessInstanceCreateLog pl2 where pl2.date > pl.date)
Big problem here: How to make the date difference calculation db independant?
1.) The way with "-" seems not to work correct with mySQL in all cases. I got some wrong figures:
2007-11-13 17:21:00.0 - 2007-11-13 17:20:59.0 = 41
2.) TIMESTAMPDIFFERENCE(SECOND, time1, time2) results in a hibernate exception (even if it should be supported):
Exception in thread "main" java.lang.NullPointerException at org.hibernate.dialect.Dialect$2.getReturnType(Dialect.java:85) at org.hibernate.hql.ast.util.SessionFactoryHelper.findFunctionReturnType(SessionFactoryHelper.java:382) at org.hibernate.hql.ast.tree.AggregateNode.getDataType(AggregateNode.java:21) at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:143) at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:705) at org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:529) at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:645) at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:281) at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:229) at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:228) at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:160) at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
3.) TIMEDIFF(time1, time2) results in a SQL exception when using MySQL:
java.sql.SQLException: Value '00:00:00 can not be represented as java.sql.Time
So how to do that in a database independant way?
The second problem is, that the figures I need are not calculated from the database. This can not be nested in the above statement (or I haven't figured it out how). So I thought about the following workaround, should work in real life, because process instance id's are ordered in order of creation date:
select count(pl), avg(pl2.date - pl.date), min(pl2.date - pl.date), max(pl2.date - pl.date), stddev(pl2.date - pl.date) from org.jbpm.graph.log.ProcessInstanceCreateLog pl, org.jbpm.graph.log.ProcessInstanceCreateLog pl2 where pl.token.processInstance.processDefinition = :processDefinition and pl2.token.processInstance.id = pl.token.processInstance.id + 1 group by (pl.token.processInstance.processDefinition)
Does anybody sees upcoming problems with that query?
The last version is what I use for the moment, but it is still not really correct.
Any ideas to the date stuff?
Or do I have to go back to native queries? This would be a pain in the ass, because I have to provide SQL's for a bunch of databases... Hibernate seems to be a bit week at the point of date calculation... By the way, I found a Hibernate JIRA issue for it, but it seems not to be assigned :-(