Hi all,
How can a get the difference of two timestamp and sum all them up. Is there a function to get the millisecond out of a timestamp field (I could not find it on the documentation)?
This is the query I'm trying to run and I get a "Wrong data type in statement":
Code:
select
str(year(startTime))||' '||str(month(startTime))||' '||str(day(startTime)),
sum( lastUpdate - startTime ) / count(*)
from JobInfo
where serviceName=:serviceName
and startTime >= :startDate
and startTime <= :endDate
group by str(year(startTime))||str(month(startTime))||str(day(startTime))
order by str(year(startTime))||str(month(startTime))||str(day(startTime)) desc
So startTime and lastUpdate are both mapped to timestamp.
Below the exception:
Code:
2008-05-12 13:21:42,605 ERROR org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:72) - Wrong data type in statement [select (cast(year(jobinfo0_.start_time) as char)||' '||cast(month(jobinfo0_.start_time) as char)||' '||cast(day(jobinfo0_.start_time) as char)) as col_0_0_, sum(jobinfo0_.last_update-jobinfo0_.start_time)/count(*) as col_1_0_ from job_info jobinfo0_ where jobinfo0_.service_name=? and jobinfo0_.start_time>=? and jobinfo0_.start_time<=? and jobinfo0_.code=8 group by (cast(year(jobinfo0_.start_time) as char)||cast(month(jobinfo0_.start_time) as char)||cast(day(jobinfo0_.start_time) as char)) order by (cast(year(jobinfo0_.start_time) as char)||cast(month(jobinfo0_.start_time) as char)||cast(day(jobinfo0_.start_time) as char)) desc]
2008-05-12 13:21:42,607 ERROR edu.sdsc.nbcr.opal.dashboard.persistence.DBManager.getResultsTimeseries(DBManager.java:309) - Error while querying for the exectime with service MEME : could not execute query
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1106)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at edu.sdsc.nbcr.opal.dashboard.persistence.DBManager.getResultsTimeseries(DBManager.java:263)
at edu.sdsc.nbcr.opal.dashboard.servlet.PloterServlet.doPost(PloterServlet.java:243)
at edu.sdsc.nbcr.opal.dashboard.servlet.PloterServlet.doGet(PloterServlet.java:136)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:690)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
at java.lang.Thread.run(Thread.java:595)
Bytheway, is there a nicer way to group by day instead of using str(year(....))str(month(...))...?
Thank you for any help,
Luca
Hibernate version: 3.1
Name and version of the database you are using: Now I'm using hsql and postgress 8
Login
FAQ
Search