Mysql query to convert total days to years, months and days

Question

I need to convert a total days to a years, months and days format using a mysql query.

My current code its this one:

SELECT  nombre, fecha_de_inicio, DATEDIFF(NOW(), fecha_de_inicio) as days_employed
FROM    cms_trabajadores 
GROUP   BY days_employed desc

Right now the result its like this (3 columns):

Name Started date and days_employed

Aaron, 2007-06-25 00:00:00, 2759

Where 1st column its name, 2nd column started date and 3rd and last column total worked days.

I'm looking to have, total working days like:

7 years, 6 months and 21 days

Thanks in advance for your help.

I do have the started date on my table and I'm always comparing vs the current day.

You should really do this in a server-sided scripting language. What are you using? — Shahar, Jan 13 '15 at 18:55
You should check this: http://stackoverflow.com/questions/10765995/convert-days-to-years-months-days-in-mysql — aimstone, Jan 13 '15 at 22:31

score 0 · Answer 1 · 2015-01-14T07:31:18.087

# UPDATE - 1:

After some corrections, the following query gave the exact result marked by you as expected:

SELECT  DISTINCT
    nombre, 
    fecha_de_inicio, 
    FLOOR(PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(fecha_de_inicio, '%Y%m')) / 12) as years_employed, 
    PERIOD_DIFF(DATE_FORMAT(DATE_ADD((CASE WHEN DAY(NOW()) >= DAY(fecha_de_inicio) THEN NOW() ELSE DATE_ADD(NOW(), INTERVAL -1 MONTH) END), INTERVAL -(FLOOR(PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(fecha_de_inicio, '%Y%m')) / 12)) YEAR), '%Y%m'), DATE_FORMAT(fecha_de_inicio, '%Y%m')) as months_employed,
    DATEDIFF(NOW(), fecha_de_inicio) - DATEDIFF(DATE_ADD(CONVERT(CONCAT(DATE_FORMAT((CASE WHEN DAY(NOW()) >= DAY(fecha_de_inicio) THEN NOW() ELSE DATE_ADD(NOW(), INTERVAL -1 MONTH) END), '%Y-%m-'), RIGHT('0' + DAY(fecha_de_inicio), 2)), DATE), INTERVAL -1 DAY), fecha_de_inicio) as days_employed
FROM    
    cms_trabajadores 
ORDER BY 
    years_employed desc, 
    months_employed desc, 
    days_employed desc

Column calculation description:

years_employed : FLOOR(PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(fecha_de_inicio, '%Y%m')) / 12) - difference in years between current date and employment date counted as difference in months divided by 12 and with remainder left out
months_employed : PERIOD_DIFF(DATE_FORMAT(DATE_ADD((CASE WHEN DAY(NOW()) >= DAY(fecha_de_inicio) THEN NOW() ELSE DATE_ADD(NOW(), INTERVAL -1 MONTH) END), INTERVAL -(FLOOR(PERIOD_DIFF(DATE_FORMAT(NOW(), '%Y%m'), DATE_FORMAT(fecha_de_inicio, '%Y%m')) / 12)) YEAR), '%Y%m'), DATE_FORMAT(fecha_de_inicio, '%Y%m')) - here, using PERIOD_DIFF function, difference in months is counted substracting first 1 month from NOW() only if DAY(NOW()) < DAY(fecha_de_inicio), then substracting the difference in years and, after that, making a DATEDIFF between the product of substraction and fecha_de_inicio; this should get the months remainder
days_employed : DATEDIFF(NOW(), fecha_de_inicio) - DATEDIFF(DATE_ADD(CONVERT(CONCAT(DATE_FORMAT((CASE WHEN DAY(NOW()) >= DAY(fecha_de_inicio) THEN NOW() ELSE DATE_ADD(NOW(), INTERVAL -1 MONTH) END), '%Y-%m-'), RIGHT('0' + DAY(fecha_de_inicio), 2)), DATE), INTERVAL -1 DAY), fecha_de_inicio) - this value is counted substracting first 1 month from NOW() only if DAY(NOW()) < DAY(fecha_de_inicio), then the new date is built using DATE_FORMAT taking Year and Month from NOW() prepared in the first step (conditional -1 Month step) and with Day taken from fecha_de_inicio substracted then by 1 DAY. Having such number of days which in fact is the difference in month between NOW() and fecha_de_inicio but converted to days, we substract this value from the DATEDIFF(NOW(), fecha_de_inicio) strict difference in days between the two dates. This gives us a days remainder

I hope it may occur helpful. In case of doubts, please write.

Hi T_G it gave me an error as days_employed its a dynamic field. MySQL Error: Unknown column 'days_employed' in 'group statement' — Jesus DLG, Jan 14 '15 at 00:19
Hi. I've finally managed to correct the answer to get the result marked by you as expected. I hope it helps. Regards. — , Jan 14 '15 at 07:32

Mysql query to convert total days to years, months and days

1 Answers1

# UPDATE - 1:

Column calculation description: