mysql查询日期计算_MySQL™ 参考手册（日期计算）

日期计算

MySQL提供了几个可用于在日期上执行计算的函数，例如，计算年龄或提取日期的部分。

要确定每只宠物的年龄，请使用TIMESTAMPDIFF()函数，它的参数是你想要表达结果的单位，以及两个可以取差的日期。以下查询显示了每只宠物的出生日期、当前日期和年龄，别名(age)用于使最终输出列标签更有意义。

mysql> SELECT name, birth, CURDATE(),

TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age

FROM pet;

+----------+------------+------------+------+

| name | birth | CURDATE() | age |

+----------+------------+------------+------+

| Fluffy | 1993-02-04 | 2019-07-09 | 26 |

| Claws | 1994-03-17 | 2019-07-09 | 25 |

| Buffy | 1989-05-13 | 2019-07-09 | 30 |

| Fang | 1990-08-27 | 2019-07-09 | 28 |

| Bowser | 1989-08-31 | 2019-07-09 | 29 |

| Chirpy | 1998-09-11 | 2019-07-09 | 20 |

| Whistler | 1997-12-09 | 2019-07-09 | 21 |

| Slim | 1996-04-29 | 2019-07-09 | 23 |

| Puffball | 1999-03-30 | 2019-07-09 | 20 |

+----------+------------+------------+------+

查询有效，但如果以某种顺序显示行，则可以更轻松地扫描结果，这可以通过添加ORDER BY name子句来按名称对输出进行排序来完成：

mysql> SELECT name, birth, CURDATE(),

TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age

FROM pet ORDER BY name;

+----------+------------+------------+------+

| name | birth | CURDATE() | age |

+----------+------------+------------+------+

| Bowser | 1989-08-31 | 2019-07-09 | 29 |

| Buffy | 1989-05-13 | 2019-07-09 | 30 |

| Chirpy | 1998-09-11 | 2019-07-09 | 20 |

| Claws | 1994-03-17 | 2019-07-09 | 25 |

| Fang | 1990-08-27 | 2019-07-09 | 28 |

| Fluffy | 1993-02-04 | 2019-07-09 | 26 |

| Puffball | 1999-03-30 | 2019-07-09 | 20 |

| Slim | 1996-04-29 | 2019-07-09 | 23 |

| Whistler | 1997-12-09 | 2019-07-09 | 21 |

+----------+------------+------------+------+

要按age而不是name对输出进行排序，只需使用不同的ORDER BY子句：

mysql> SELECT name, birth, CURDATE(),

TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age

FROM pet ORDER BY age;

+----------+------------+------------+------+

| name | birth | CURDATE() | age |

+----------+------------+------------+------+

| Chirpy | 1998-09-11 | 2019-07-09 | 20 |

| Puffball | 1999-03-30 | 2019-07-09 | 20 |

| Whistler | 1997-12-09 | 2019-07-09 | 21 |

| Slim | 1996-04-29 | 2019-07-09 | 23 |

| Claws | 1994-03-17 | 2019-07-09 | 25 |

| Fluffy | 1993-02-04 | 2019-07-09 | 26 |

| Fang | 1990-08-27 | 2019-07-09 | 28 |

| Bowser | 1989-08-31 | 2019-07-09 | 29 |

| Buffy | 1989-05-13 | 2019-07-09 | 30 |

+----------+------------+------------+------+

类似的查询可用于确定死亡动物的死亡年龄，你可以通过检查death值是否为NULL来确定这些动物，然后，对于那些具有非NULL值的，计算death和birth值之间的差值：

mysql> SELECT name, birth, death,

TIMESTAMPDIFF(YEAR,birth,death) AS age

FROM pet WHERE death IS NOT NULL ORDER BY age;

+--------+------------+------------+------+

| name | birth | death | age |

+--------+------------+------------+------+

| Bowser | 1989-08-31 | 1995-07-29 | 5 |

+--------+------------+------------+------+

查询使用death IS NOT NULL而不是death <> NULL，因为NULL是一个特殊值，无法使用通常的比较运算符进行比较。

如果你想知道哪些动物下个月过生日怎么办？对于这种类型的计算，年和日是无关紧要的，你只想提取birth列的月份部分，MySQL提供了几个用于提取日期部分的函数，例如YEAR()，MONTH()和DAYOFMONTH()。这里MONTH()是适当的方法，要查看它是如何工作的，运行一个简单的查询，显示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 |

+----------+------------+--------------+

在接下来的一个月里寻找有生日的动物也很简单，假设当前月份是4月，然后月份值为4，你可以像这样查找5月(第5个月)出生的动物：

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

+-------+------------+

| name | birth |

+-------+------------+

| Buffy | 1989-05-13 |

+-------+------------+

如果当前月份是12月，则会出现一个小的复杂情况，你不能仅仅在月份数字(12)中添加1并查找在第13个月出生的动物，因为没有这样的月份，相反，你寻找1月(第1个月)出生的动物。

你可以编写查询，以便无论当前月份是什么，它都可以工作，这样你就不必使用特定月份的数字，DATE_ADD()使你可以将时间间隔添加到给定日期。如果你将一个月添加到CURDATE()的值，然后使用MONTH()提取月份部分，结果将生成查找生日的月份：

mysql> SELECT name, birth FROM pet

WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

完成相同任务的另一种方法是在使用模数函数(MOD)将月值包装为0(如果它当前为12)后，添加1以获取当前一个之后的下一个月：

mysql> SELECT name, birth FROM pet

WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

MONTH()返回1到12之间的数字，MOD(something,12)返回0到11之间的数字，因此，添加必须在MOD()之后，否则我们将从11月(11)到1月(1)。

如果计算使用无效日期，则计算失败并生成警告：

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' |

+---------+------+----------------------------------------+