0

I want to calculate total experience year and month of each particular user id. user_id can be multiple in the table like the following. Here for user_id 14, the total experience range will be from 2010-01-03 to 2013-11-30 and the expected result will be 3 year 11 month. How can I do it in either php or mysql?

enter image description here

Md. Shamvil Hossain
  • 340
  • 6
  • 24
  • 3
    http://stackoverflow.com/questions/5279079/mysql-convert-timediff-output-to-day-hour-minute-second-format – jewelhuq May 11 '16 at 02:03
  • If you want further help on top of what @jewelhuq has suggested you need to create an SQL Fiddle (http://sqlfiddle.com/) with sample data and edit your question to show expected output based on your sample data. – Alex May 11 '16 at 02:07
  • In mySQL the is a function datediff - http://dev.mysql.com/doc/en/date-and-time-functions.html –  May 11 '16 at 02:08
  • Is `exp_to` nullable? – Arcesilas May 11 '16 at 03:37

1 Answers1

3

Comments are relevant but incomplete for your case.

If exp_to is not nullable:

select user_id, sum( datediff(exp_to, exp_from) +1 )
from experience
group by user_id;

Notes:

  • don't forget +1 in the sum, because for user 25 it would return 0, whereas we can decently consider the user worked 1 day
  • for user 14, based on your screenshot, the total is 880 days: you'll have to calculate the number of months (with PHP, for example).

If exp_to is nullable (which makes sense if user is still working at the same position):

select user_id, sum( datediff( ifnull(exp_to, now()), exp_from) +1 )
from experience
group by user_id;
Arcesilas
  • 1,388
  • 11
  • 24