Human age function tip
I couldn't find this anywhere, seems like something folks might have use for. Age in human-style: we speak of newborns in days, infants in weeks then later months, then finally we speak in terms of years. This function does just that, easily modified to suit preferences in your area or even to be made flexible about the hardcoded boundaries. Returns the age suffixed with units.
bd short for is birthdate
set global log_bin_trust_function_creators=1;
DROP FUNCTION IF EXISTS age_human;
DELIMITER //
CREATE FUNCTION age_human (bd datetime) returns char(10)
BEGIN
SET @age = DATEDIFF(CURDATE(), bd); #age in days
IF @age < 14 THEN RETURN CONCAT(@age, 'd'); #return age in days if less than 14d
ELSEIF @age < 56 THEN RETURN CONCAT(FLOOR(@age/7),'w'); #return age in weeks if less than 8 weeks
ELSE
SET @age = PERIOD_DIFF(CONCAT( YEAR(CURDATE()), SUBSTRING(CURDATE(),6,2) ), CONCAT(YEAR(bd), SUBSTRING(bd,6,2) ) ); #age in months
END IF;
IF @age < 37 THEN return CONCAT(@age, 'm'); #return age in months if less than 37months
ELSE return CONCAT( ((YEAR(CURDATE())-YEAR(bd)) - (RIGHT(CURDATE(),5)<RIGHT(bd,5))), 'y'); #otherwise return age in years
END IF;
END//
DELIMITER ;
DROP TABLE IF EXISTS person;
CREATE TABLE `person` (
`name` char(20) default NULL,
`dob` date default NULL
);
INSERT INTO person VALUES ('baby1', SUBDATE(CURDATE(), 9) ), ('baby2', SUBDATE(CURDATE(),75) ), ('toddler1', SUBDATE(CURDATE(), 400) ), ('other1', SUBDATE(CURDATE(), 4000) );
SELECT name, dob, curdate(), age_human(dob) FROM person;