MySQL 计算今年是否为闰年

小咪咪 2022-11-21 04:10 183阅读 0赞

备注:测试数据库版本为MySQL 8.0

# 一.问题: #

求今年是否为闰年

# 二.解决方案 #

根据2月份最后一天来判断

select  day(
            last_day(
            date_add(
            date_add(
            date_add(current_date,
                     interval - dayofyear(current_date) day),
                     interval 1 day),
                     interval 1 month))) dy;

测试记录

mysql> select  day(
        ->         last_day(
        ->         date_add(
        ->         date_add(
        ->         date_add(current_date,
        ->                  interval - dayofyear(current_date) day),
        ->                  interval 1 day),
        ->                  interval 1 month))) dy;
    +------+
    | dy   |
    +------+
    |   29 |
    +------+
    1 row in set (0.01 sec)

**这样看起来有点麻烦，分解开**  
dayofyear(current\_date) – 求出今天是本年的第多少天  
date\_add(date, interval - n day) – 今天的天数减去截止当前是今年多少天 得出上年的12月31日  
date\_add(date, interval + 1 day) – 得出今年1月的第1天  
date\_add(date, interval + 1 month) – 得出今年2月的第一天  
last\_day(date) – 得出2月份最后一天  
day(date) --求出日

测试记录:

mysql> select dayofyear(current_date);
    +-------------------------+
    | dayofyear(current_date) |
    +-------------------------+
    |                     289 |
    +-------------------------+
    1 row in set (0.00 sec)
    
    mysql> select date_add(current_date,interval -289 day);
    +------------------------------------------+
    | date_add(current_date,interval -289 day) |
    +------------------------------------------+
    | 2019-12-31                               |
    +------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql>
    mysql> select date_add('2019-12-31',interval 1 day);
    +---------------------------------------+
    | date_add('2019-12-31',interval 1 day) |
    +---------------------------------------+
    | 2020-01-01                            |
    +---------------------------------------+
    1 row in set (0.00 sec)
    
    mysql>
    mysql> select date_add('2020-01-01',interval 1 month);
    +-----------------------------------------+
    | date_add('2020-01-01',interval 1 month) |
    +-----------------------------------------+
    | 2020-02-01                              |
    +-----------------------------------------+
    1 row in set (0.00 sec)
    
    mysql>
    mysql> select last_day('2020-02-01');
    +------------------------+
    | last_day('2020-02-01') |
    +------------------------+
    | 2020-02-29             |
    +------------------------+
    1 row in set (0.00 sec)
    
    mysql> select day('2020-02-29');
    +-------------------+
    | day('2020-02-29') |
    +-------------------+
    |                29 |
    +-------------------+
    1 row in set (0.00 sec)