14 Replies Latest reply on Nov 20, 2007 5:51 AM by Bernd Ruecker

    [sim] problems with hibernate queries and dates

    Bernd Ruecker Master


      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 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:

       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 :-(