Ich habe dieses Problem bereits im englischen Forum geschildert, da wusste aber niemand eine gute Antwort. Ich weiss man sollte das nicht tun, aber ich probiers jetzt halt hier nochmals:

Ich möchte einige Datumsberechnungen in HQL machen. Hier ein Beispiel:


Ich möchte das später in einigen Reporting-Queries verwenden. Zusammen mit avg() oder sum(). Deshalb möchte ich das auch nicht in Java erledigen. Ich würe sonst SQL als Alternative wählen.

Wenn ich das generierte SQL-Statement (siehe unten) direkt auf der DB ausführe, dann kriege ich ein float o.ä. zurück. Er stellt die Anzahl Tage zwischen den beiden Dates dar. Deshalb kann Hibernat das auch nicht so einfach in ein java.util.Date umwandeln.
Wie kann ich Hibernate dazu bringen, die Berechnung vorzunehmen?

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