How can we create a MySQL function to find out the duration of years, months, days, hours, minutes and seconds?
Following is a MySQL function which calculates the duration in years, months, days, hours, minutes and seconds between two dates.
mysql> DROP FUNCTION IF EXISTS Duration;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DROP FUNCTION IF EXISTS Label123;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DELIMITER //
mysql> CREATE FUNCTION Duration( dtd1 datetime, dtd2 datetime ) RETURNS CHAR(128)
-> BEGIN
-> DECLARE yyr,mon,mmth,dy,ddy,hhr,m1,ssc,t1 BIGINT;
-> DECLARE dtmp DATETIME;
-> DECLARE t0 TIMESTAMP;
-> SET yyr = TIMESTAMPDIFF(YEAR,dtd1,dtd2);
-> SET mon = TIMESTAMPDIFF(MONTH,dtd1,dtd2);
-> SET mmth = mon MOD 12;
-> SET dtmp = ADDDATE(dtd1, interval mon MONTH);
-> SET dy = TIMESTAMPDIFF(DAY,dtd1,dtd2);
-> SET ddy = TIMESTAMPDIFF(DAY,dtmp,dtd2);
-> SET t0 = TIMESTAMPADD(DAY,dy,dtd1);
-> SET t1 = TIME_TO_SEC(TIMEDIFF(dtd2,t0));
-> SET hhr = FLOOR(t1/3600);
-> SET m1 = FLOOR(t1/60) - 60*hhr;
-> SET ssc = t1 - 3600*hhr - 60*m1;
-> RETURN CONCAT( Label123(yyr,'year'), Label123(mmth,'month'),
-> label123(ddy,'day'), Label123(hhr,'hour'),
-> Label123(m1,'min'), Label123(ssc,'sec')
-> );
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FUNCTION Label123( ival int, clabel char(16) ) RETURNS VARCHAR(24)
-> RETURN Concat( ival, ' ', clabel, If(ival=1,' ','s ') ); //
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> Select Duration('2000-08-04 06:09:46', '2011-07-01 05:05:36')AS 'Duration';
+-----------------------------------------------------+
| Duration |
+-----------------------------------------------------+
| 10 years 10 months 26 days 22 hours 55 mins 50 secs |
+-----------------------------------------------------+
1 row in set (0.00 sec)
Published on 19-Feb-2018 16:22:36
