MySQL provides several functions that you can use to perform calculations on dates, for example, to calculate ages or extract parts of dates.MySQL提供了几个函数，可以用来计算日期，例如，计算年龄或提取部分日期。
To determine how many years old each of your pets is, use the 要确定每个宠物的年龄，请使用TIMESTAMPDIFF() function. TIMESTAMPDIFF()函数。Its arguments are the unit in which you want the result expressed, and the two dates for which to take the difference. 它的参数是表示结果的单位，以及求差的两个日期。The following query shows, for each pet, the birth date, the current date, and the age in years. 以下查询显示每只宠物的出生日期、当前日期和年龄（以年为单位）。An alias (别名（age) is used to make the final output column label more meaningful.age）用于使最终输出列标签更有意义。
mysql>SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS ageFROM pet;+----------+------------+------------+------+ | name | birth | CURDATE() | age | +----------+------------+------------+------+ | Fluffy | 1993-02-04 | 2003-08-19 | 10 | | Claws | 1994-03-17 | 2003-08-19 | 9 | | Buffy | 1989-05-13 | 2003-08-19 | 14 | | Fang | 1990-08-27 | 2003-08-19 | 12 | | Bowser | 1989-08-31 | 2003-08-19 | 13 | | Chirpy | 1998-09-11 | 2003-08-19 | 4 | | Whistler | 1997-12-09 | 2003-08-19 | 5 | | Slim | 1996-04-29 | 2003-08-19 | 7 | | Puffball | 1999-03-30 | 2003-08-19 | 4 | +----------+------------+------------+------+
The query works, but the result could be scanned more easily if the rows were presented in some order. 查询可以工作，但如果按某种顺序显示行，则可以更轻松地扫描结果。This can be done by adding an 这可以通过添加ORDER BY name clause to sort the output by name:ORDER by name子句按名称对输出进行排序来实现：
mysql>SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS ageFROM pet ORDER BY name;+----------+------------+------------+------+ | name | birth | CURDATE() | age | +----------+------------+------------+------+ | Bowser | 1989-08-31 | 2003-08-19 | 13 | | Buffy | 1989-05-13 | 2003-08-19 | 14 | | Chirpy | 1998-09-11 | 2003-08-19 | 4 | | Claws | 1994-03-17 | 2003-08-19 | 9 | | Fang | 1990-08-27 | 2003-08-19 | 12 | | Fluffy | 1993-02-04 | 2003-08-19 | 10 | | Puffball | 1999-03-30 | 2003-08-19 | 4 | | Slim | 1996-04-29 | 2003-08-19 | 7 | | Whistler | 1997-12-09 | 2003-08-19 | 5 | +----------+------------+------------+------+
To sort the output by 要按age rather than name, just use a different ORDER BY clause:age而不是name对输出进行排序，只需使用不同的ORDER BY子句：
mysql>SELECT name, birth, CURDATE(),TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS ageFROM pet ORDER BY age;+----------+------------+------------+------+ | name | birth | CURDATE() | age | +----------+------------+------------+------+ | Chirpy | 1998-09-11 | 2003-08-19 | 4 | | Puffball | 1999-03-30 | 2003-08-19 | 4 | | Whistler | 1997-12-09 | 2003-08-19 | 5 | | Slim | 1996-04-29 | 2003-08-19 | 7 | | Claws | 1994-03-17 | 2003-08-19 | 9 | | Fluffy | 1993-02-04 | 2003-08-19 | 10 | | Fang | 1990-08-27 | 2003-08-19 | 12 | | Bowser | 1989-08-31 | 2003-08-19 | 13 | | Buffy | 1989-05-13 | 2003-08-19 | 14 | +----------+------------+------------+------+
A similar query can be used to determine age at death for animals that have died. 类似的查询可用于确定已死亡动物的死亡年龄。You determine which animals these are by checking whether the 您可以通过检查death value is NULL. death值是否为NULL来确定这些动物是哪些动物。Then, for those with non-然后，对于具有非NULL values, compute the difference between the death and birth values:NULL值的值，计算death值和birth值之间的差值：
mysql>SELECT name, birth, death,TIMESTAMPDIFF(YEAR,birth,death) AS ageFROM pet WHERE death IS NOT NULL ORDER BY age;+--------+------------+------------+------+ | name | birth | death | age | +--------+------------+------------+------+ | Bowser | 1989-08-31 | 1995-07-29 | 5 | +--------+------------+------------+------+
The query uses 查询使用death IS NOT NULL rather than death <> NULL because NULL is a special value that cannot be compared using the usual comparison operators. death IS NOT NULL而不是death <> NULL，因为NULL是一个特殊值，不能使用常用的比较运算符进行比较。This is discussed later. 这将在后面讨论。See Section 3.3.4.6, “Working with NULL Values”.请参阅第3.3.4.6节，“使用空值”。
What if you want to know which animals have birthdays next month? 如果你想知道哪些动物下个月有生日呢？For this type of calculation, year and day are irrelevant; you simply want to extract the month part of the 对于这种类型的计算，年和日是不相关的；您只需要提取birth column. birth列的月份部分。MySQL provides several functions for extracting parts of dates, such as MySQL提供了几个提取日期部分的函数，例如YEAR(), MONTH(), and DAYOFMONTH(). YEAR()、MONTH()和DAYOFMONTH()。MONTH() is the appropriate function here. MONTH()是此处的适当函数。To see how it works, run a simple query that displays the value of both 要查看其工作原理，请运行一个简单的查询，其中显示birth and MONTH(birth):birth和MONTH(birth)的值：
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 | 1989-08-31 | 8 |
| Chirpy | 1998-09-11 | 9 |
| Whistler | 1997-12-09 | 12 |
| Slim | 1996-04-29 | 4 |
| Puffball | 1999-03-30 | 3 |
+----------+------------+--------------+
Finding animals with birthdays in the upcoming month is also simple. 在接下来的一个月里找到有生日的动物也很简单。Suppose that the current month is April. 假设当前月份是四月。Then the month value is 然后月值为4，您可以查找5月（第4 and you can look for animals born in May (month 5) like this:5个月）出生的动物，如下所示：
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
There is a small complication if the current month is December. 如果当前月份是12月，则会出现一个小问题。You cannot merely add one to the month number (你不能仅仅在月数（12) and look for animals born in month 13, because there is no such month. 12）上加1，然后寻找在第13个月出生的动物，因为没有这样的月份。Instead, you look for animals born in January (month 相反，你要寻找一月出生的动物（第1).1个月）。
You can write the query so that it works no matter what the current month is, so that you do not have to use the number for a particular month. 您可以编写查询，这样无论当前月份是什么，查询都可以工作，这样您就不必使用特定月份的数字。DATE_ADD() enables you to add a time interval to a given date. DATE_ADD()允许您向给定日期添加时间间隔。If you add a month to the value of 如果将月份添加到CURDATE(), then extract the month part with MONTH(), the result produces the month in which to look for birthdays:CURDATE()的值中，然后使用month()提取月份部分，则结果将生成要查找生日的月份：
mysql>SELECT name, birth FROM petWHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
A different way to accomplish the same task is to add 完成相同任务的另一种方法是，在使用模函数（1 to get the next month after the current one after using the modulo function (MOD) to wrap the month value to 0 if it is currently 12:MOD）将月份值包装为0（如果当前为12）后，添加1以获得当前月份之后的下一个月：
mysql>SELECT name, birth FROM petWHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
MONTH() returns a number between 1 and 12. MONTH()返回一个介于1和12之间的数字。And MOD(something,12) returns a number between 0 and 11. MOD(something,12)返回一个介于0和11之间的数字。So the addition has to be after the 因此加法必须在MOD(), otherwise we would go from November (11) to January (1).MOD()之后，否则我们将获得从11月（11）到1月（1）。
If a calculation uses invalid dates, the calculation fails and produces warnings:如果计算使用无效日期，则计算失败并产生警告：
mysql>SELECT '2018-10-31' + INTERVAL 1 DAY;+-------------------------------+ | '2018-10-31' + INTERVAL 1 DAY | +-------------------------------+ | 2018-11-01 | +-------------------------------+ mysql>SELECT '2018-10-32' + INTERVAL 1 DAY;+-------------------------------+ | '2018-10-32' + INTERVAL 1 DAY | +-------------------------------+ | NULL | +-------------------------------+ mysql>SHOW WARNINGS;+---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1292 | Incorrect datetime value: '2018-10-32' | +---------+------+----------------------------------------+