MySQL提供了一些函数，可用于对日期执行计算，例如，计算年龄或提取部分日期。
根据出生日期计算年龄
计算年龄需要用到 TIMESTAMPDIFF()函数。它的参数是要表示结果的单位，以及两个日期之间的差值。
计算结果等于CURDATE()-birth
单位是year。
mysql> SELECT name, birth, CURDATE(),
TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
FROM pet order by name;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Bowser | 1979-08-31 | 2020-08-04 | 40 |
| Buffy | 1989-05-13 | 2020-08-04 | 31 |
| Chirpy | 1998-09-11 | 2020-08-04 | 21 |
| Claws | 1994-03-17 | 2020-08-04 | 26 |
| Fang | 1990-08-27 | 2020-08-04 | 29 |
| Fluffy | 1993-02-04 | 2020-08-04 | 27 |
| Puffball | 1999-03-30 | 2020-08-04 | 21 |
| Slim | 1996-04-29 | 2020-08-04 | 24 |
| Whistler | 1997-12-09 | 2020-08-04 | 22 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)
单位是month
mysql> SELECT name, birth, CURDATE(),TIMESTAMPDIFF(MONTH,birth,CURDATE()) AS age_month FROM
pet order by name;
+----------+------------+------------+-----------+
| name | birth | CURDATE() | age_month |
+----------+------------+------------+-----------+
| Bowser | 1979-08-31 | 2020-08-04 | 491 |
| Buffy | 1989-05-13 | 2020-08-04 | 374 |
| Chirpy | 1998-09-11 | 2020-08-04 | 262 |
| Claws | 1994-03-17 | 2020-08-04 | 316 |
| Fang | 1990-08-27 | 2020-08-04 | 359 |
| Fluffy | 1993-02-04 | 2020-08-04 | 330 |
| Puffball | 1999-03-30 | 2020-08-04 | 256 |
| Slim | 1996-04-29 | 2020-08-04 | 291 |
| Whistler | 1997-12-09 | 2020-08-04 | 271 |
+----------+------------+------------+-----------+
9 rows in set (0.00 sec)
分别获得日期中的年月日
获取日期中的年 YEAR()
mysql> SELECT name, birth, YEAR(birth) FROM pet;
+----------+------------+-------------+
| name | birth | YEAR(birth) |
+----------+------------+-------------+
| Fluffy | 1993-02-04 | 1993 |
| Claws | 1994-03-17 | 1994 |
| Buffy | 1989-05-13 | 1989 |
| Fang | 1990-08-27 | 1990 |
| Bowser | 1979-08-31 | 1979 |
| Chirpy | 1998-09-11 | 1998 |
| Whistler | 1997-12-09 | 1997 |
| Slim | 1996-04-29 | 1996 |
| Puffball | 1999-03-30 | 1999 |
+----------+------------+-------------+
9 rows in set (0.00 sec)
获取日期中的月 MONTH()
mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name | birth | MONTH(birth) |
+----------+------------+--------------+
| Fluffy | 1993-02-04 | 2 |
| Claws | 1994-03-17 | 3 |
| Buffy | 1989-05-13 | 5 |
| Fang | 1990-08-27 | 8 |
| Bowser | 1979-08-31 | 8 |
| Chirpy | 1998-09-11 | 9 |
| Whistler | 1997-12-09 | 12 |
| Slim | 1996-04-29 | 4 |
| Puffball | 1999-03-30 | 3 |
+----------+------------+--------------+
9 rows in set (0.00 sec)
获取日期中的日 DAY()
mysql> SELECT name, birth, DAY(birth) FROM pet;
+----------+------------+------------+
| name | birth | DAY(birth) |
+----------+------------+------------+
| Fluffy | 1993-02-04 | 4 |
| Claws | 1994-03-17 | 17 |
| Buffy | 1989-05-13 | 13 |
| Fang | 1990-08-27 | 27 |
| Bowser | 1979-08-31 | 31 |
| Chirpy | 1998-09-11 | 11 |
| Whistler | 1997-12-09 | 9 |
| Slim | 1996-04-29 | 29 |
| Puffball | 1999-03-30 | 30 |
+----------+------------+------------+
9 rows in set (0.00 sec)
INTERVAL计算日期
mysql> SELECT '2020-07-31' + INTERVAL 1 DAY;
+-------------------------------+
| '2020-07-31' + INTERVAL 1 DAY |
+-------------------------------+
| 2020-08-01 |
+-------------------------------+
1 row in set (0.00 sec)
mysql>
如果计算使用无效日期，则计算将失败并产生警告
mysql> SELECT '2020-07-32' + INTERVAL 1 DAY;
+-------------------------------+
| '2020-07-32' + INTERVAL 1 DAY |
+-------------------------------+
| NULL |
+-------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2020-07-32' |
+---------+------+----------------------------------------+
1 row in set (0.01 sec)
mysql>
