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

[camunda]

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#4104230

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4104230