日期计算
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' |
+---------+------+----------------------------------------+
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。