首页 > 代码库 > [MySQL]-->查询5天之内过生日的同事中的闰年2月29日问题的解决过程

[MySQL]-->查询5天之内过生日的同事中的闰年2月29日问题的解决过程

前言:

上次写了查询5天之内过生日的同事中的跨年问题的解决过程,网址为:http://blog.csdn.net/mchdba/article/details/38952033 ,居康甩脂机怎么样其中漏了一个闰年2月29日生日的细节问题,现在补充一下这个问题的处理过程:

5,补充闰年判断

有朋友提醒,闰年2月29日生日的话,可能查询不到,想到确实没有考虑到这个特殊的日期。
5.1,准备测试数据SQL,包含1980-02-29这一天生日的朋友。
INSERT INTO ali_users  SELECT ‘Jeff‘,‘1980-02-29‘,‘13998786549‘ 
UNION ALL SELECT ‘WeiYa‘,‘1980-02-28‘,‘13998786549‘ 
UNION ALL SELECT ‘XiaoTeng‘,‘1980-03-01‘,‘13998786549‘ 
UNION ALL SELECT ‘HeSheng‘,‘1980-03-02‘,‘13998786549‘
UNION ALL SELECT ‘JingPan‘,‘1980-03-03‘,‘13998786549‘
UNION ALL SELECT ‘WuHong‘,‘1986-03-04‘,‘13998786549‘;

5.2,录入测试数据

  1. mysql> use test;

  2. Reading table information for completion of table and column names

  3. You can turn off this feature to get a quicker startup with -A


  4. Database changed

  5. mysql> INSERT INTO ali_users SELECT \‘Jeff\‘,\‘1980-02-29\‘,\‘13998786549\‘ UNION ALL SELECT \‘XiaoTeng\‘,\‘1980-03-01\‘,\‘13998786549\‘ 

  6.     -> UNION ALL SELECT \‘HeSheng\‘,\‘1980-03-02\‘,\‘13998786549\‘

  7.     -> UNION ALL SELECT \‘JingPan\‘,\‘1980-03-03\‘,\‘13998786549\‘

  8.     -> UNION ALL SELECT \‘WuHong\‘,\‘1986-03-04\‘,\‘13998786549\‘;

  9. Query OK, 5 rows affected (0.00 sec)

  10. Records: 5 Duplicates: 0 Warnings: 0


  11. mysql>


5.3,执行原来的旧版本的SQL查询检查结果

把Step#2中的SQL的NOW()改成‘2014-02-28 00:10:10‘来进行测试,确实没有包括2月29日生日的同事,如下所示:

  1. mysql> SELECT * FROM ali_users WHERE 

  2.     -> DATEDIFF(CAST(CONCAT(YEAR(\‘2014-02-28 00:10:10\‘),DATE_FORMAT(birthday,\‘-%m-%d\‘))AS DATE),CAST(DATE_FORMAT(\‘2014-02-28 00:10:10\‘,\‘%y-%m-%d\‘) AS DATE)) BETWEEN 0 AND 4

  3.     -> OR/* or后面的是捎带解决跨年问题*/ 

  4.     -> DATEDIFF(CAST(CONCAT(YEAR(\‘2014-02-28 00:10:10\‘)+1,DATE_FORMAT(birthday,\‘-%m-%d\‘))AS DATE),CAST(DATE_FORMAT(\‘2014-02-28 00:10:10\‘,\‘%y-%m-%d\‘) AS DATE)) BETWEEN 0 AND 4

  5.     -> ;

  6. +----------+------------+-------------+

  7. | username | birthday | iphone |

  8. +----------+------------+-------------+

  9. | XiaoTeng | 1980-03-01 | 13998786549 |

  10. | HeSheng | 1980-03-02 | 13998786549 |

  11. | JingPan | 1980-03-03 | 13998786549 |

  12. | WuHong | 1986-03-04 | 13998786549 |

  13. +----------+------------+-------------+

  14. 4 rows in set, 2 warnings (0.00 sec)



  15. mysql>


5.4,先建立一个存储函数f_isleap_year判断当年年份是否是闰年
5.4.1 准备判断是否闰年的SQL

  1. DELIMITER $$

  2. USE `test`$$

  3. DROP FUNCTION IF EXISTS `f_not_leap_year`$$

  4. CREATE FUNCTION `f_not_leap_year`(p_year BIGINT) RETURNS BOOLEAN

  5. BEGIN

  6. /*是闰年则返回0(false),不是闰年则返回1(true)*/

  7. DECLARE v_flag INT DEFAULT 0;

  8. /*①、普通年能被4整除且不能被100整除的为闰年。(如2004年就是闰年,1901年不是闰年)*/

  9. IF (p_year%4)=AND (p_year%100)>THEN

  10. SET v_flag=0; 

  11. /*②、世纪年能被400整除的是闰年。(如2000年是闰年,1900年不是闰年) */

  12. ELSEIF (p_year%400)=THEN

  13. SET v_flag=0; 

  14. /*③、对于数值很大的年份,这年如果能整除3200,并且能整除172800则是闰年。如172800年是闰年,

  15. 86400年不是闰年(因为虽然能整除3200,但不能整除172800)(此按一回归年365天5h48\‘45.5\‘\‘计算)。

  16. */

  17. ELSEIF (p_year%3200)=AND (p_year%172800)=THEN

  18. SET v_flag=0;

  19. ELSE

  20. SET v_flag=1;

  21. END IF;

  22. RETURN v_flag;

  23.  END$$

  24. DELIMITER ;


存储函数执行如下图所示:
 


5.4.2 准备SQL语句

  1. SELECT * FROM ali_users WHERE 

  2. DATEDIFF(CAST(CONCAT(YEAR(NOW()),DATE_FORMAT(birthday,\‘-%m-%d\‘))AS DATE),CAST(DATE_FORMAT(NOW(),\‘%y-%m-%d\‘) AS DATE)) BETWEEN 0 AND 4

  3. OR/* or后面的是捎带解决跨年问题*/ 

  4. DATEDIFF(CAST(CONCAT(YEAR(NOW())+1,DATE_FORMAT(birthday,\‘-%m-%d\‘))AS DATE),CAST(DATE_FORMAT(NOW(),\‘%y-%m-%d\‘) AS DATE)) BETWEEN 0 AND 4

  5. OR /*补充闰年2月29日的生日问题*/

  6. (

  7. f_not_leap_year(YEAR(NOW()))

  8. AND DATE_FORMAT(birthday,\‘-%m-%d\‘)=\‘-02-29\‘ 

  9. AND DATEDIFF(CAST(CONCAT(\‘2000\‘,DATE_FORMAT(birthday,\‘-%m-%d\‘))AS DATE),CAST(CONCAT(\‘2000\‘,DATE_FORMAT(NOW(),\‘-%m-%d\‘)) AS DATE)) 

  10. BETWEEN 0 AND 4

  11. );


5.4.3 在非闰年的时候,验证闰年2月29日生日,选择2014年非闰年测试
把Step#2中的SQL的NOW()改成‘2014-02-28 00:10:10‘来进行测试,SQL如下所示:
SELECT * FROM ali_users WHERE 
DATEDIFF(CAST(CONCAT(YEAR(‘2014-02-28 00:10:10‘),DATE_FORMAT(birthday,‘-%m-%d‘))AS DATE),CAST(DATE_FORMAT(‘2014-02-28 00:10:10‘,‘%y-%m-%d‘) AS DATE)) BETWEEN 0 AND 4
OR/* or后面的是捎带解决跨年问题*/ 
DATEDIFF(CAST(CONCAT(YEAR(‘2014-02-28 00:10:10‘)+1,DATE_FORMAT(birthday,‘-%m-%d‘))AS DATE),CAST(DATE_FORMAT(‘2014-02-28 00:10:10‘,‘%y-%m-%d‘) AS DATE)) BETWEEN 0 AND 4
OR /*补充闰年2月29日的生日方法*/
(
f_not_leap_year(YEAR(‘2014-02-28 00:10:10‘)) 
AND DATE_FORMAT(birthday,‘-%m-%d‘)=‘-02-29‘ 
AND DATEDIFF(CAST(CONCAT(‘2000‘,DATE_FORMAT(birthday,‘-%m-%d‘))AS DATE),CAST(CONCAT(‘2000‘,DATE_FORMAT(‘2000-02-28 00:10:10‘,‘-%m-%d‘)) AS DATE)) 
BETWEEN 0 AND 4
);

执行SQL检验成果,如果当天是2014-02-28,看到已经有2月29日的生日的同事被记录进来了,其实包含了2月28日、2月29日、3月1日、3月2日、3月3日、3月4日的生日的同事。
PS:因为2月29日在当年不存在,所以不算这5天之内的范畴,执行结果如下所示:

  1. mysql> SELECT * FROM ali_users WHERE 

  2.     -> DATEDIFF(CAST(CONCAT(YEAR(\‘2014-02-28 00:10:10\‘),DATE_FORMAT(birthday,\‘-%m-%d\‘))AS DATE),CAST(DATE_FORMAT(\‘2014-02-28 00:10:10\‘,\‘%y-%m-%d\‘) AS DATE)) BETWEEN 0 AND 4

  3.     -> OR/* or后面的是捎带解决跨年问题*/ 

  4.     -> DATEDIFF(CAST(CONCAT(YEAR(\‘2014-02-28 00:10:10\‘)+1,DATE_FORMAT(birthday,\‘-%m-%d\‘))AS DATE),CAST(DATE_FORMAT(\‘2014-02-28 00:10:10\‘,\‘%y-%m-%d\‘) AS DATE)) BETWEEN 0 AND 4

  5.     -> OR /*补充闰年2月29日的生日方法*/

  6.     -> (

  7.     -> f_not_leap_year(YEAR(\‘2014-02-28 00:10:10\‘)) 

  8.     -> AND DATE_FORMAT(birthday,\‘-%m-%d\‘)=\‘-02-29\‘ 

  9.     -> AND DATEDIFF(CAST(CONCAT(\‘2000\‘,DATE_FORMAT(birthday,\‘-%m-%d\‘))AS DATE),CAST(CONCAT(\‘2000\‘,DATE_FORMAT(\‘2000-02-28 00:10:10\‘,\‘-%m-%d\‘)) AS DATE)) 

  10.     -> BETWEEN 0 AND 4

  11.     -> );

  12. +----------+------------+-------------+

  13. | username | birthday | iphone |

  14. +----------+------------+-------------+

  15. | Jeff | 1980-02-29 | 13998786549 |

  16. | XiaoTeng | 1980-03-01 | 13998786549 |

  17. | HeSheng | 1980-03-02 | 13998786549 |

  18. | JingPan | 1980-03-03 | 13998786549 |

  19. | WuHong | 1986-03-04 | 13998786549 |

  20. | WeiYa | 1980-02-28 | 13998786549 |

  21. +----------+------------+-------------+

  22. 6 rows in set, 2 warnings (0.00 sec)



  23. mysql>


5.4.4 在闰年的时候,验证闰年2月29日生日,选择2004年闰年测试
把Step#2中的SQL的NOW()改成‘2004-02-28 00:10:10‘来进行测试,SQL如下所示:

  1. SELECT * FROM ali_users WHERE 

  2. DATEDIFF(CAST(CONCAT(YEAR(\‘2004-02-28 00:10:10\‘),DATE_FORMAT(birthday,\‘-%m-%d\‘))AS DATE),CAST(DATE_FORMAT(\‘2004-02-28 00:10:10\‘,\‘%y-%m-%d\‘) AS DATE)) BETWEEN 0 AND 4

  3. OR/* or后面的是捎带解决跨年问题*/ 

  4. DATEDIFF(CAST(CONCAT(YEAR(\‘2004-02-28 00:10:10\‘)+1,DATE_FORMAT(birthday,\‘-%m-%d\‘))AS DATE),CAST(DATE_FORMAT(\‘2004-02-28 00:10:10\‘,\‘%y-%m-%d\‘) AS DATE)) BETWEEN 0 AND 4

  5. OR /*补充闰年2月29日的生日方法*/

  6. (

  7. f_not_leap_year(YEAR(\‘2004-02-28 00:10:10\‘)) 

  8. AND DATE_FORMAT(birthday,\‘-%m-%d\‘)=\‘-02-29\‘ 

  9. AND DATEDIFF(CAST(CONCAT(\‘2000\‘,DATE_FORMAT(birthday,\‘-%m-%d\‘))AS DATE),CAST(CONCAT(\‘2000\‘,DATE_FORMAT(\‘2004-02-28 00:10:10\‘,\‘-%m-%d\‘)) ASDATE)) 

  10. BETWEEN 0 AND 4

  11. );


执行SQL检验成果,如果当天是2004-02-28,看到已经有2月29日的生日的同事被记录进来了,其实包含了2月28日、2月29日、3月1日、3月2日、3月3日的生日的同事。
PS:因为2月29日在当年存在,所以算这5天之内的范畴,执行结果如下所示:

  1. mysql> SELECT * FROM ali_users WHERE 

  2.     -> DATEDIFF(CAST(CONCAT(YEAR(\‘2004-02-28 00:10:10\‘),DATE_FORMAT(birthday,\‘-%m-%d\‘))AS DATE),CAST(DATE_FORMAT(\‘2004-02-28 00:10:10\‘,\‘%y-%m-%d\‘) AS DATE)) BETWEEN 0 AND 4

  3.     -> OR/* or后面的是捎带解决跨年问题*/ 

  4.     -> DATEDIFF(CAST(CONCAT(YEAR(\‘2004-02-28 00:10:10\‘)+1,DATE_FORMAT(birthday,\‘-%m-%d\‘))AS DATE),CAST(DATE_FORMAT(\‘2004-02-28 00:10:10\‘,\‘%y-%m-%d\‘) AS DATE)) BETWEEN 0 AND 4

  5.     -> OR /*补充闰年2月29日的生日方法*/

  6.     -> (

  7.     -> f_not_leap_year(YEAR(\‘2004-02-28 00:10:10\‘)) 

  8.     -> AND DATE_FORMAT(birthday,\‘-%m-%d\‘)=\‘-02-29\‘ 

  9.     -> AND DATEDIFF(CAST(CONCAT(\‘2000\‘,DATE_FORMAT(birthday,\‘-%m-%d\‘))AS DATE),CAST(CONCAT(\‘2000\‘,DATE_FORMAT(\‘2004-02-28 00:10:10\‘,\‘-%m-%d\‘)) AS DATE)) 

  10.     -> BETWEEN 0 AND 4

  11.     -> );

  12. +----------+------------+-------------+

  13. | username | birthday | iphone |

  14. +----------+------------+-------------+

  15. | Jeff | 1980-02-29 | 13998786549 |

  16. | XiaoTeng | 1980-03-01 | 13998786549 |

  17. | HeSheng | 1980-03-02 | 13998786549 |

  18. | JingPan | 1980-03-03 | 13998786549 |

  19. | WeiYa | 1980-02-28 | 13998786549 |

  20. +----------+------------+-------------+

  21. 5 rows in set (0.00 sec)



  22. mysql>


[MySQL]-->查询5天之内过生日的同事中的闰年2月29日问题的解决过程