Reworded title: Calculating the experience an employee accumulates across multiple jobs

Question

I have a table called employee experience with columns id, userId, startDate and endDate.

I want to calculate the work experience of employees. Can anyone help provide code for mysql query or JPA specification?

For example, for the following data:

id	User ID	Start Date	End date
1	1	2021-01-01	2022-01-01
2	2	2019-01-01	2020-01-01
3	2	2020-01-02	2021-01-01
4	3	2021-01-01	2022-01-01

The output should be:

User ID	Experience
1	1
2	2
3	1

Answer 1

Successfully accomplished this using the following code:

SELECT SUM(TIMESTAMPDIFF(YEAR, START_DATE, END_DATE)) AS experience,
          SOCIAL_PROFILE_ID
   FROM tableName
   GROUP BY SOCIAL_PROFILE_ID