वर्ष, महीना, दिन, घंटा, मिनट और सेकंड की अवधि ज्ञात करने के लिए MySQL फंक्शन कैसे बनाएं?

निम्नलिखित एक MySQL फंक्शन है जो दो तारीखों के बीच वर्ष, महीना, दिन, घंटा, मिनट और सेकंड में अवधि की गणना करता है।

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)