[SOLVED] Average of difference between two dates using Hibernate JPA in SQL Server

Average of difference between two dates using Hibernate JPA in SQL Server

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.

Solution

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