mysql - How to find difference between two values in one column by cross referencing against values in another column -

i have table looks this:

id | machine | date | status ---------------------------- 1  | 1 | 01-01-2009 | new 1  | 1 | 01-01-2010 | busted 1  | 2 | 01-01-2010 | new 1  | 1 | 01-01-2012 | repaired 

i need know differences in date between each status per machine, if recent status , there no newer status machine, needs show difference between last date listed , current date:

id | machine | date | status   | duration --------------------------------------- 1  | 1 | 01-01-2009 | new      | 1 year 1  | 1 | 01-01-2010 | busted   | 2 years 1  | 2 | 01-01-2010 | new      | 2 years, 2 months, 5 days 1  | 1 | 01-01-2012 | repaired | 2 months, 5 days 

to quite honest, don't care how new duration column displays distances between dates. attempted make query myself failed.

this give values in days -

select *, datediff(ifnull((select `date` machine_status ms2 ms1.machine = ms2.machine , ms1.`date` < ms2.`date` order `date` asc limit 1), current_date), ms1.`date`) machine_status ms1;