DROP FUNCTION IF EXISTS PeriodLen;
DROP FUNCTION IF EXISTS NumLabel;
DELIMITER go
CREATE FUNCTION PeriodLen( dt1 datetime, dt2 datetime ) RETURNS CHAR(128)
BEGIN
  DECLARE yy,m0,mm,d0,dd,hh,mi,ss,t1 BIGINT;
  DECLARE dtmp DATETIME;
  DECLARE t0 TIMESTAMP;
  SET yy = TIMESTAMPDIFF(YEAR,dt1,dt2);
  SET m0 = TIMESTAMPDIFF(MONTH,dt1,dt2);
  SET mm = m0 MOD 12;
  SET dtmp = ADDDATE(dt1, interval m0 MONTH);
  SET d0 = TIMESTAMPDIFF(DAY,dt1,dt2);
  SET dd = TIMESTAMPDIFF(DAY,dtmp,dt2); 
  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( NumLabel(yy,'year'), NumLabel(mm,'month'), 
                 Numlabel(dd,'day'), NumLabel(hh,'hour'), 
                 NumLabel(mi,'min'), NumLabel(ss,'sec') 
               );
END;
go
CREATE FUNCTION NumLabel( ival int, clabel char(16) ) RETURNS VARCHAR(24)
RETURN Concat( ival, ' ', clabel, If(ival=1,' ','s ') );
go
DELIMITER ;
SELECT PeriodLen( '2024-7-1 20:34:35', '2024-08-24 15:53:42' );
+---------------------------------------------------------+
| PeriodLen( '2024-7-1 20:34:35', '2024-08-24 15:53:42' ) |
+---------------------------------------------------------+
| 11 years 1 month 22 days 19 hours 19 mins 7 secs        |
+---------------------------------------------------------+

Last updated 20 Jun 2024