MySQL :: MySQL 9.0 参考手册 :: 5.3.4.5 日期计算

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / 日期计算

5.3.4.5 日期计算

MySQL 提供了许多函数，可用于对日期进行计算，例如计算年龄或提取日期的各个部分。

要确定每只宠物的年龄，请使用 TIMESTAMPDIFF() 函数。它的参数是您希望结果以其表示的单位以及要计算差值的两个日期。以下查询显示了每只宠物的出生日期、当前日期和年龄（以年为单位）。使用了一个别名 (age) 来使最终的输出列标签更具意义。

mysql> SELECT name, birth, CURDATE(),
       TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
       FROM 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 |
+----------+------------+------------+------+

该查询有效，但如果以某种顺序呈现行，结果将更容易扫描。这可以通过添加一个 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 | 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 |
+----------+------------+------------+------+

要按 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 | 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 |
+----------+------------+------------+------+

类似的查询可用于确定已死亡动物的死亡年龄。您可以通过检查 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 是一个特殊值，不能使用常规的比较运算符进行比较。这将在后面讨论。请参阅第 5.3.4.6 节，“处理 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，您可以查找出生在五月份（月份 5）的动物，如下所示。

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+

如果当前月份是十二月，则有一个小问题。您不能简单地将月份数字 (12) 加 1，然后查找出生在月份 13 的动物，因为没有这样的月份。相反，您需要查找出生在月份 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) 到一月 (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' |
+---------+------+----------------------------------------+