MYSQL自定義函數(shù)之返回兩個(gè)時(shí)間的指定類型時(shí)差

DELIMITER $$

CREATE FUNCTION DateBetween
 (AType INT, ADateStart DATETIME, ADateEnd DATETIME)
RETURNS BIGINT
BEGIN
DECLARE AResult BIGINT DEFAULT -1;
/*入?yún)Type INT類型，結(jié)果類型 1:年 2:月 3:日 4:小時(shí) 5:分鐘 6:秒鐘 (必須在1-6內(nèi)，否則結(jié)果返回-1)
*入?yún)DateStart DATETIME類型，起始時(shí)間 (必須是一個(gè)正確的日期參數(shù))
*入?yún)DateEnd DATETIME類型，結(jié)束時(shí)間 (必須是一個(gè)正確的日期參數(shù))
*返回值 BIGINT類型，返回起始之間與結(jié)束時(shí)間的指定時(shí)差 (成功返回計(jì)算出的時(shí)差，失敗返回-1)
*/
IF ADateStart != '' AND ADateEnd != '' AND AType >=1 AND AType <= 6 THEN
 CASE AType
 WHEN 1 THEN
 SELECT TIMESTAMPDIFF(YEAR, ADateStart, ADateEnd) INTO AResult; 
 WHEN 2 THEN 
 SELECT TIMESTAMPDIFF(MONTH, ADateStart, ADateEnd) INTO AResult; 
 WHEN 3 THEN
 SELECT TIMESTAMPDIFF(DAY, ADateStart, ADateEnd) INTO AResult; 
 WHEN 4 THEN
 SELECT TIMESTAMPDIFF(HOUR, ADateStart, ADateEnd) INTO AResult; 
 WHEN 5 THEN
 SELECT TIMESTAMPDIFF(MINUTE, ADateStart, ADateEnd) INTO AResult; 
 WHEN 6 THEN
 SELECT TIMESTAMPDIFF(SECOND, ADateStart, ADateEnd) INTO AResult; 
 END CASE;
END IF;
RETURN(AResult); 
END$$

SELECT DateBetween(0, '2010-01-01 00:00:00', '2010-01-01 10:00:00');
SELECT DateBetween(1, '2001-01-01', '2010-01-01');
SELECT DateBetween(2, '2010-01-01', '2010-12-01');
SELECT DateBetween(3, '2010-01-01', '2010-01-31');
SELECT DateBetween(4, '2010-01-01 00:00:00', '2010-01-01 10:00:00');
SELECT DateBetween(5, '2010-01-01 00:00:00', '2010-01-01 10:00:00');
SELECT DateBetween(6, '2010-01-01 00:00:00', '2010-01-01 10:00:00');
SELECT DateBetween(7, '2010-01-01 00:00:00', '2010-01-01 10:00:00');