Hi,
i want to make some date calculation for reporting purposes. Here is an example:


I want to use this later to calculate average, sum etc. If this doesn't work in HQL, I'll have to do it in SQL. It would cost a lot of performance to do this stuff in java.

Hibernate version:
Hibernate 3.0.2
Mapping documents:
<hibernate-mapping
>
<class
name="com.optitrans.ejb.model.TransportTask"
table="OT_Transportauftrag"
dynamic-update="false"
dynamic-insert="false"
select-before-update="false"
optimistic-lock="version"
>
<id
name="id"
column="ID"
type="java.lang.Long"
unsaved-value="null"
>
<generator class="seqhilo">
</generator>
</id>

<version
name="version"
type="java.util.Date"
column="Version"
access="property"
unsaved-value="undefined"
/>

<property
name="actualArrival"
type="java.util.Date"
update="true"
insert="true"
access="property"
column="AnkunftszeitTatsaechlich"
/>

<property
name="actualDeparture"
type="java.util.Date"
update="true"
insert="true"
access="property"
column="AbholzeitTatsaechlich"
/>
[......]
</class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

HQL-Query: select t.actualArrival-t.actualDeparture from TransportTask t


Full stack trace of any exception that occurs:
10:08:40,921 WARN [JDBCExceptionReporter] SQL Error: 0, SQLState: 07006
10:08:40,921 ERROR [JDBCExceptionReporter] Error converting to timestamp.
10:08:40,953 INFO [STDOUT] org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1564)
at org.hibernate.loader.Loader.list(Loader.java:1544)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:830)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at com.optitrans.ejb.persistence.TestQueryDAOHibernate.executeQuery(TestQueryDAOHibernate.java:37)
at com.optitrans.ejb.service.TestManagerBean.executeHibernateQuery(TestManagerBean.java:105)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.invocation.Invocation.performCall(Invocation.java:345)
at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:214)
at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:185)
at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:130)
at org.jboss.webservice.server.ServiceEndpointInterceptor.invoke(ServiceEndpointInterceptor.java:51)
at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:48)
at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:105)
at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:335)
at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:166)
at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:139)
at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:192)
at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:624)
at org.jboss.ejb.Container.invoke(Container.java:873)
at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:413)
at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:88)
at $Proxy137.executeHibernateQuery(Unknown Source)
at com.optitrans.web.service.TestManagerBD.executeHibernateQuery(TestManagerBD.java:66)
at com.optitrans.web.action.TestQueryAction.execute(TestQueryAction.java:60)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:39)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:153)
at com.optitrans.web.tomcat.TransferPrincipalValve.invoke(TransferPrincipalValve.java:29)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482)
at com.optitrans.web.tomcat.TransferPrincipalValve.invoke(TransferPrincipalValve.java:29)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:59)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
at java.lang.Thread.run(Thread.java:595)
Caused by: org.firebirdsql.jdbc.field.TypeConvertionException: Error converting to timestamp.
at org.firebirdsql.jdbc.field.FBField.getTimestamp(FBField.java:631)
at org.firebirdsql.jdbc.FBResultSet.getTimestamp(FBResultSet.java:520)
at org.hibernate.type.TimestampType.get(TimestampType.java:30)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
at org.hibernate.loader.hql.QueryLoader.getResultColumnOrRow(QueryLoader.java:332)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:321)
at org.hibernate.loader.Loader.doQuery(Loader.java:395)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:210)
at org.hibernate.loader.Loader.doList(Loader.java:1561)
... 58 more

Name and version of the database you are using:
Firebird 1.5.2.4731

The generated SQL (show_sql=true):
SQL: select transportt0_.AnkunftszeitTatsaechlich-transportt0_.AbholzeitTatsaechlich as col_0_0_ from OT_Transportauftrag transportt0_

Debug level Hibernate log excerpt:
I can provide full logging if needed

When I execute the generated SQL directly on the Database, it returns a float (or something) that indicates the number of days between the two dates. E.g. 2.5 for 2 days 12 hours. How do I tell hibernate the Type of the result of the calculation. Obviously Hibernate interprets the result of a subtraction of two dates as date, but the database returns a numeric.

When I execute the generated SQL directly on the Database, it returns a float (or something) that indicates the number of days between the two dates. E.g. 2.5 for 2 days 12 hours. How do I tell hibernate the Type of the result of the calculation. Obviously Hibernate interprets the result of a subtraction of two dates as date, but the database returns a numeric.

try this

Well, that gives me an

I think the cast(.. as ..) function is only allowed in the where-clause :-(

I think I'll do a wordaround: I'll create an attribute "duration" which saves the duration of a TransportTask, additionally to the start and end times.