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
)并查找月份 13
的动物,因为没有这样的月份。相反,您查找一月(月份 1
)生日的动物。
您可以编写查询,使其在任何当前月份下都能工作,不需要使用特定月份的数字。DATE_ADD()
允许您将时间间隔添加到给定的日期。如果您将一个月添加到 CURDATE()
的值,然后使用 MONTH()
提取月份部分,结果将产生查找生日的月份:
mysql> SELECT name, birth FROM pet
WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
完成相同任务的另一种方法是将 1
添加到当前月份后,然后使用模块函数 (MOD
) 将月份值折叠到 0
,如果当前月份是 12
:
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' |
+---------+------+----------------------------------------+