MySql生日闰月处理

1. 科普下润年：

①、非整百年能被4整除的为闰年。（如2004年就是闰年,2100年不是闰年）

②、整百年能被400整除的是闰年。(如2000年是闰年，1900年不是闰年)

 

2. 例：

例如：当前日期是2016年8月21日，有人出生在1972年2月29日，查询后该用户最近的生日应该是2017年3月1日(非闰年)。如果当前日期是2016年1月20日，那么查询后应该返回2016年2月29日(闰年)。

 

3. 初始数据

先安装mysql官方示例数据库employees。没安装的可以参考：《MAC安装MYSQL官方示例数据库EMPLOYEE》

• 脚本操作：

-- 创建表employees
create table employees like employees.employees;
-- 将employees库的employees表数据插入到自己的表
insert into employees
    select * from employees.employees limit 0,10;
-- 新增数据，生日为闰年1972-02-29
insert into employees
    select '10011','1972-02-29','Jiang','David','M','1990-2-20';

4. 查询用户和出生信息

• 语句：

-- 查询用户和出生信息
select concat(e.last_name, '  ', e.first_name) as Name, e.birth_date as BirthDate from employees e;

• 查询结果：

5. 实现

　　5.1 查询当前日期、当前日期和生日间隔的年数。

• • 代码：

  • select concat(e.last_name, '  ', e.first_name) as Name, 
        e.birth_date  as BirthDay,
        (year(now())-year(e.birth_date)) diff,
        now() as today
    from employees e

  • 结果：

　　　　　　

　　　　　　　　

　　5.2 查询当年的生日和下一年的生日。

• • 代码：

    select name,birthday,today,
        date_add(birthday, interval diff year) curr, -- 当年生日
        date_add(birthday, interval diff+1 year) next    -- 下一年生日
    from (
        select concat(e.last_name, '  ', e.first_name) as Name, 
            e.birth_date  as BirthDay,
            (year(now())-year(e.birth_date)) diff,
            now() as today
        from employees e
    ) as a

  • 查询结果

　　　　　　

　　　5.3 出生日期是29日，当年或下一年生日是28日，就将生日日期加1天

• • 代码：

    select name,birthday,today,
        date_add(curr, interval if(day(birthday)=29 && day(curr)=28, 1, 0) day) as cur, -- 闰年运行后的当年生日
        date_add(next, interval if(day(birthday)=29 && day(next)=28, 1, 0) day) as next -- 闰年运行后的下一年生日
    from (
        select name,birthday,today,
            date_add(birthday, interval diff year) curr,  -- 当年生日
            date_add(birthday, interval diff+1 year) next -- 下一年生日
        from (
        select concat(e.last_name, '  ', e.first_name) as Name, 
            e.birth_date  as BirthDay,
            (year(now())-year(e.birth_date)) diff,
            now() as today
        from employees e
        ) as a
    ) as b

  • 查询结果：

　　　　　　

　　5.4 最终代码，如果当年生日已经过了就返回下一年生日。

• • 代码：

    select name,birthday,
        if(cur>today, cur, next) as birth_day -- 如果当年生日大于当前日期，生日为今年，否则为下一年
    from(
        select name,birthday,today,
            date_add(curr, interval if(day(birthday)=29 && day(curr)=28, 1, 0) day) as cur, -- 闰年运行后的当年生日
            date_add(next, interval if(day(birthday)=29 && day(next)=28, 1, 0) day) as next -- 闰年运行后的下一年生日
        from (
            select name,birthday,today,
                date_add(birthday, interval diff year) curr,  -- 当年生日
                date_add(birthday, interval diff+1 year) next -- 下一年生日
            from (
            select concat(e.last_name, '  ', e.first_name) as Name, 
                e.birth_date  as BirthDay,
                (year(now())-year(e.birth_date)) diff,
                now() as today
            from employees e
            ) as a
        ) as b
    ) as c

  • 查询结果：