功能:
统计两个日期间有多少个2月29
实现方法:
2月29天数 = 两个日期间天数 - (两个日期在非闰年的一个自然年内天数 + 365 * 两个日期年数差)
DELIMITER $$
DROP FUNCTION IF EXISTS `F_GET_LEAYYEAR_DAYS`$$
CREATE FUNCTION `F_GET_LEAYYEAR_DAYS`(start_date BIGINT, end_date BIGINT) RETURNS INT(11)
BEGIN
DECLARE v_day INT;
DECLARE v_year INT;
DECLARE v_base_day INT;
DECLARE v_leay_year_days INT;
DECLARE v_start_date DATETIME;
DECLARE v_end_date DATETIME;
SET v_start_date = start_date;
SET v_end_date = end_date;
IF start_date > end_date THEN
RETURN -1;
END IF;
SELECT TIMESTAMPDIFF(DAY, start_date, end_date) INTO v_day;
SELECT (DATE_FORMAT(end_date, '%Y') - DATE_FORMAT(start_date, '%Y')) INTO v_year;
IF DATE_FORMAT(start_date, '%m%d') = '0229' THEN
SET v_start_date = DATE_FORMAT(start_date, '%Y-03-01');
END IF;
IF DATE_FORMAT(end_date, '%m%d') = '0229' THEN
SET v_end_date = DATE_FORMAT(end_date, '%Y-%m-28');
END IF;
IF v_year > 0 THEN
SET @s_date = DATE_FORMAT(v_start_date, '2014-%m-%d');
SET @e_date = DATE_FORMAT(v_end_date, '2015-%m-%d');
ELSE
SET @s_date = DATE_FORMAT(v_start_date, '2015-%m-%d');
SET @e_date = DATE_FORMAT(v_end_date, '2015-%m-%d');
END IF;
SELECT TIMESTAMPDIFF(DAY, @s_date, @e_date) INTO v_base_day;
IF v_year > 0 THEN
SELECT v_day - (v_base_day + 365 * (v_year - 1)) INTO v_leay_year_days;
ELSE
SELECT v_day - (v_base_day + 365 * v_year) INTO v_leay_year_days;
END IF;
RETURN v_leay_year_days;
END$$
DELIMITER ;
测试用例:
SELECT start_date
, end_date
, F_GET_LEAYYEAR_DAYS(start_date, end_date) AS days
FROM (
SELECT 20110201 AS start_date, 20111001 AS end_date
UNION ALL
SELECT 20110201 AS start_date, 20121001 AS end_date
UNION ALL
SELECT 20120229 AS start_date, 20120229 AS end_date
UNION ALL
SELECT 20120229 AS start_date, 20130228 AS end_date
UNION ALL
SELECT 20110201 AS start_date, 20171001 AS end_date
) t ;