1.3.4.5 日期运算

MySQL 提供一些函数用于日期的运算，比如说：计算年龄或提供日期中的年、月、日信息；

比方说，您想知道所有宠物的年龄，那么您可以使用 TIMESTAMPDIFF() 函数，该函数有3个参数，分别是提取的单位、开始日期、结束日期，下面的语句显示了每一个宠物的 name、birth、CURDATE()、age(别名) ：

mysql> select name,CURDATE(),birth,TIMESTAMPDIFF(YEAR,birth, CURDATE()) as age  
    -> from pet;
+----------------------+------------+------------+------+
| name                 | CURDATE()  | birth      | age  |
+----------------------+------------+------------+------+
| Fluffy               | 2017-12-20 | 1993-02-04 |   24 |
| Claws                | 2017-12-20 | 1994-03-17 |   23 |
| Buffy                | 2017-12-20 | 1989-05-13 |   28 |
| Fang                 | 2017-12-20 | 1990-08-27 |   27 |
| Bowser               | 2017-12-20 | 1989-08-30 |   28 |
| Chirpy               | 2017-12-20 | 1998-09-11 |   19 |
| Whistler             | 2017-12-20 | 1997-12-09 |   20 |
| Slim                 | 2017-12-20 | 1996-04-29 |   21 |
| Puffball             | 2017-12-20 | 1999-03-30 |   18 |
+----------------------+------------+------------+------+
9 rows in set (0.01 sec)

Note

TIMESTAMPDIFF 函数的第一个参数可选值包括：MICROSECOND (微秒)、SECOND、MINUTE、HOUR、DAY、WEEK, MONTH、QUARTER（季度）、YEAR；其中 MICROSECOND 是5.0版本替换 FRAC_SECOND（毫秒），5.5 版本之后 FRAC_SECOND 正式废弃。第二、第三参数必需 Y-m-d H:i:s 的日期格式 或其省略格式，例如：Y-m-d、Y-m-d H等。

对上面的检索结果进行排序，将更方便浏览检索结果：

按照 name 进行排序：

mysql> select name,CURDATE(),birth,TIMESTAMPDIFF(YEAR,birth, CURDATE()) as age
    -> from pet
    -> order by name;
+----------------------+------------+------------+------+
| name                 | CURDATE()  | birth      | age  |
+----------------------+------------+------------+------+
| Bowser               | 2017-12-20 | 1989-08-30 |   28 |
| Buffy                | 2017-12-20 | 1989-05-13 |   28 |
| Chirpy               | 2017-12-20 | 1998-09-11 |   19 |
| Claws                | 2017-12-20 | 1994-03-17 |   23 |
| Fang                 | 2017-12-20 | 1990-08-27 |   27 |
| Fluffy               | 2017-12-20 | 1993-02-04 |   24 |
| Puffball             | 2017-12-20 | 1999-03-30 |   18 |
| Slim                 | 2017-12-20 | 1996-04-29 |   21 |
| Whistler             | 2017-12-20 | 1997-12-09 |   20 |
+----------------------+------------+------------+------+
9 rows in set (0.00 sec)

按照 age 降序排序

mysql> select name,CURDATE(),birth,TIMESTAMPDIFF(YEAR,birth, CURDATE()) as age
    -> from pet
    -> order by age desc;
+----------------------+------------+------------+------+
| name                 | CURDATE()  | birth      | age  |
+----------------------+------------+------------+------+
| Buffy                | 2017-12-20 | 1989-05-13 |   28 |
| Bowser               | 2017-12-20 | 1989-08-30 |   28 |
| Fang                 | 2017-12-20 | 1990-08-27 |   27 |
| Fluffy               | 2017-12-20 | 1993-02-04 |   24 |
| Claws                | 2017-12-20 | 1994-03-17 |   23 |
| Slim                 | 2017-12-20 | 1996-04-29 |   21 |
| Whistler             | 2017-12-20 | 1997-12-09 |   20 |
| Chirpy               | 2017-12-20 | 1998-09-11 |   19 |
| Puffball             | 2017-12-20 | 1999-03-30 |   18 |
+----------------------+------------+------------+------+
9 rows in set (0.00 sec)

相似的，我们要检索已死亡宠物的年龄，那么首先，要确定宠物是否死亡？判断 death 是否为 NULL。其次使用函数 TIMESTAMPDIFF ,计算死亡年龄。

mysql> select name,birth,death,
    -> TIMESTAMPDIFF(YEAR,birth,death) as age
    -> from pet
    -> where death is not NULL
    -> order by age desc;
+----------------------+------------+------------+------+
| name                 | birth      | death      | age  |
+----------------------+------------+------------+------+
| Bowser               | 1989-08-30 | 1995-07-29 |    5 |
+----------------------+------------+------------+------+
1 row in set (0.00 sec)

Note

在 MySQL 中，NULL是一个特殊的值，不能使用常用的算术运行符进行比较，如 <> NULL、!= NULL等 ,都不能得到期望的结果，在下一节中，我们将介绍 MySQL 中的 NULL 值。

如果您想知道下个月有哪些宠物需要过生日？对于这种计算和年份、日份无关，我们仅需要知道月份。在 MySQL 中提供了多个函数，用于提取部分日期，例如：year()、month()、day()、dayofmonth()，下面是一个简单示例，显示宠物的出生日期和出生月份。

mysql> select name,birth,month(birth) as birthMonth 
    -> from pet
    -> order by birthMonth;
+----------------------+------------+------------+
| name                 | birth      | birthMonth |
+----------------------+------------+------------+
| Fluffy               | 1993-02-04 |          2 |
| Claws                | 1994-03-17 |          3 |
| Puffball             | 1999-03-30 |          3 |
| Slim                 | 1996-04-29 |          4 |
| Buffy                | 1989-05-13 |          5 |
| Fang                 | 1990-08-27 |          8 |
| Bowser               | 1989-08-30 |          8 |
| Chirpy               | 1998-09-11 |          9 |
| Whistler             | 1997-12-09 |         12 |
+----------------------+------------+------------+
9 rows in set (0.00 sec)

mysql> select name,birth,day(birth) as birthDay from pet;
mysql> select name,birth,year(birth) as birthYear from pet;

如果您要查询当前 8 月份，有哪些宠物生日，那么您也可以执行如下的操作：

mysql> SELECT name, birth, month(birth) as monthBirth FROM pet WHERE month(birth) = 8;
+----------------------+------------+------------+
| name                 | birth      | monthBirth |
+----------------------+------------+------------+
| Fang                 | 1990-08-27 |          8 |
| Bowser               | 1989-08-30 |          8 |
+----------------------+------------+------------+
2 rows in set (0.00 sec)

Tips:

此处您可能会有一个疑问，为什么不使用 SELECT name, birth, month(birth) as monthBirth FROM pet WHERE monthBirth = 8; 如果您执行该语句，会出现 ERROR 1054 (42S22): Unknown column 'monthBirth' in 'where clause' 的错误。但是，您使用 SELECT name, birth, month(birth) as monthBirth FROM pet order by monthBirth; 却能显示正常结果，即别名分别出现在 where、order by 的不同字句中。

那么，别名到底出现在哪些字句中可以执行？哪些字句中不能执行？我们需要了解 SELECT 的执行顺序，请阅读：1.3.4.10 SELECT的执行顺序

您还可以进行其他的一些日期运算

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

本章的内容仅是介绍日期运算的入门知识，更详细关于日期的运算请看后续章节。