3.3.4.5 日期计算
MySQL 提供了一些函数,可用于对日期执行计算,例如,计算年龄或提取部分日期。
要确定您的每只宠物几岁,请使用TIMESTAMPDIFF()功能。它的参数是要 table 示结果的单位,以及两个日期之间的差值。以下查询为每只宠物显示出生日期,当前日期和年龄(以岁为单位)。 * alias *(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 |
+----------+------------+------------+------+
该查询有效,但是如果以某种 Sequences 显示行,则可以更轻松地扫描结果。这可以通过添加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
是一个特殊值,无法使用常规比较运算符进行比较。稍后讨论。参见第 3.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 月。然后,月份值为4
,您可以像这样查找 5 月(5
月份)出生的动物:
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
如果当前月份为十二月,则情况会很复杂。您不能仅在月份号(12
)上加一个,并查找在月份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 月(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' |
+---------+------+----------------------------------------+