2

I'm having a problem with aggregate SUM function in JPA (Eclipselink v.2.1.2) which is executed on MySql database. I need to sum values in a field of type 'TIME'. But, executed query returns wrong result. For example, I have two values ('04:15:00' and '05:50:00') and result is 96500. Query (JPQL) is:

SELECT SUM(w.timeSpent) FROM WorkingHours w

What is the proper way to sum Time values in JPA?

Thanks in advance.

Vladimir
  • 305
  • 1
  • 7
  • 16

2 Answers2

2

In EclipseLink (>=2.1) you could try,

SELECT FUNC('SEC_TO_TIME', SUM(FUNC('TIME_TO_SEC', w.timeSpent))) FROM WorkingHours w
James
  • 17,965
  • 11
  • 91
  • 146
1

Is the result a Time or a Integer?

What SQL is generated, and what does MySQL return for this? Could be just what MySQL is returning. Try a native SQL query with the same SQL.

James
  • 17,965
  • 11
  • 91
  • 146
  • Well, the result is Double. When I run SQL in MySQL console which should give me sum (SELECT timeSpent FROM WorkingHours) I also get result which is not in TIME format. I suppose there is other way to calculate sum in MySQL, but I'm not sure if JPQL could perform the same thing. – Vladimir Jan 05 '11 at 09:26
  • It seems there is no way I could do it in JPQL. Native query should look like this: SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(w.TimeSpent))) FROM WorkingHours w – Vladimir Jan 05 '11 at 12:05