1

I'm having a table with columns: id, task_type, start_time, end_time. I need to get the Average Time taken by each task. In SQL query I could do:

SELECT task_type, AVG(DATEDIFF(MS, end_time, start_time)) AS average_time GROUP BY task_type

I'm unable to use the DATEDIFF in JPA as it's not supported. If I pass the nativeQury to the JPA "@Query" It shows the column names which are not included in SELECT statement as Invalid, suppose if I pass the aforementioned query as native query I get error the column name id is not valid.

It would be a great help if anyone is able to point out what I'm missing here.

Note: The project is a micro service architecture, database is stored in a Microsoft SQL Server and the API is being written in Spring Boot.

Vinayaka S P
  • 177
  • 3
  • 13

3 Answers3

1

Regarding calling vendor-specific and user-defined functions, see this tutorial on ways you can do that from JPQL.

The function() syntax might not work for you due to the MS parameter (you could probably register a Hibernate user type for that, which is a nuissance). Registering a function in the dialect should work just fine, though. You'll probably want to implement SQLFunction yourself, rather than using the StandardSQLFunction implementation.

As for why the native query doesn't work for you, what is the result class you're trying to map your query onto? Try using an interface or a DTO with the same property names as the aliases used in the query, similar to how you would use projections. If that doesn't work, a result set mapping should.

crizzis
  • 9,978
  • 2
  • 28
  • 47
0

The way I'd write that in JPQL:

 SELECT ent.taskType, AVG( EXTRACT (EPOCH FROM (ent.endTime - ent.startTime)))
 FROM Entity ent
 GROUP BY ent.taskType

This is the result in seconds, you can manipulate de extract result to whatever you want to show in view.

Matt
  • 1
  • 2
0

I solved this by creating the Projection class and JPQL query.

Vinayaka S P
  • 177
  • 3
  • 13