ฟังก์ชัน 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)
