Duration in years, months, days and time
DROP FUNCTION IF EXISTS PeriodLen;
DELIMITER |
CREATE FUNCTION PeriodLen( dt1 datetime, dt2 datetime ) RETURNS CHAR(128)
BEGIN
DECLARE yy,mm,d0,dd,hh,mi,ss,t1 BIGINT;
DECLARE t0 TIMESTAMP;
SET yy = TIMESTAMPDIFF(YEAR,dt1,dt2);
SET mm = TIMESTAMPDIFF(MONTH,dt1,dt2) MOD 12;
SET d0 = TIMESTAMPDIFF(DAY,dt1,dt2);
SET dd = d0 MOD (yy*mm);
SET t0 = TIMESTAMPADD(DAY,d0,dt1);
SET t1 = TIME_TO_SEC(TIMEDIFF(dt2,t0));
SET hh = FLOOR(t1/3600);
SET mi = FLOOR(t1/60) - 60*hh;
SET ss = t1 - 3600*hh - 60*mi;
RETURN CONCAT( yy, ' years ', mm, ' months ', dd, ' days ', hh, ' hours ', mi, ' mins ', ss, ' secs' );
END |
DELIMITER ;
SELECT PeriodLen( '2000-6-1 20:34:35',now() );
+----------------------------------------------------+
| PeriodLen( '2000-6-1 20:34:35',now() ) |
+----------------------------------------------------+
| 8 years 11 months 19 days 17 hours 49 mins 22 secs |
+----------------------------------------------------+