[jboss-dev-forums] [Design of JBoss jBPM] - [sim] problems with hibernate queries and dates

Hi. 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 :-( http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434 View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4104230#... Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&a...