3.3.4.5 Date Calculations
MySQL提供一系列的函数你可以运行来计算日期,例如,来计算年龄或提取日期部分。
决定你的每个宠物的年龄有多大,使用TIMESTAMPDIFF()函数,他的参数是哪部分单元你想解释,和两个日期你想比较不同的。下面的查询展示子,每个宠物,出生日期,当前日期,年龄。别名(age)用来使最终的输出列的标签更加有意义。
mysql> select name,birth, curdate(), timestampdiff(year, birth, curdate()) as age from pet;
+----------+------------+------------+------+
| name | birth | curdate() | age |
+----------+------------+------------+------+
| Fluffy | 1993-02-04 | 2017-08-23 | 24 |
| Claws | 1994-03-17 | 2017-08-23 | 23 |
| Buffy | 1989-05-13 | 2017-08-23 | 28 |
| Fang | 1990-08-27 | 2017-08-23 | 26 |
| Bowser | 1989-08-31 | 2017-08-23 | 27 |
| Chirpy | 1998-09-11 | 2017-08-23 | 18 |
| Whistler | 1997-12-09 | 2017-08-23 | 19 |
| Slim | 1996-04-29 | 2017-08-23 | 21 |
| Puffball | 1999-03-30 | 2017-08-23 | 18 |
+----------+------------+------------+------+
查询工作了,但是如果结果可以被更加容易的浏览如果行以某种排序呈现的化。这可以通过添加ORDER BY name语句通过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 | 2017-08-23 | 27 |
| Buffy | 1989-05-13 | 2017-08-23 | 28 |
| Chirpy | 1998-09-11 | 2017-08-23 | 18 |
| Claws | 1994-03-17 | 2017-08-23 | 23 |
| Fang | 1990-08-27 | 2017-08-23 | 26 |
| Fluffy | 1993-02-04 | 2017-08-23 | 24 |
| Puffball | 1999-03-30 | 2017-08-23 | 18 |
| Slim | 1996-04-29 | 2017-08-23 | 21 |
| Whistler | 1997-12-09 | 2017-08-23 | 19 |
+----------+------------+------------+------+
以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 | 2017-08-23 | 18 |
| Puffball | 1999-03-30 | 2017-08-23 | 18 |
| Whistler | 1997-12-09 | 2017-08-23 | 19 |
| Slim | 1996-04-29 | 2017-08-23 | 21 |
| Claws | 1994-03-17 | 2017-08-23 | 23 |
| Fluffy | 1993-02-04 | 2017-08-23 | 24 |
| Fang | 1990-08-27 | 2017-08-23 | 26 |
| Bowser | 1989-08-31 | 2017-08-23 | 27 |
| Buffy | 1989-05-13 | 2017-08-23 | 28 |
+----------+------------+------------+------+
一个简单的查询可以用来发现已经死亡的动物的年龄。你决定哪些动物死了通过检查death值是否为NULL.然后,对于那些death列不是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是一特殊值在通常的比较操作中不能被比较。这在稍后会讨论。查阅Section 3.3.4.6 "Working with NULL Values"。
当你想知道哪个动物下个月过生日时?像这种类型的计算,年和日是无关紧要的;你仅需要提取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月出生的动物:
mysql> select name, birth from pet where month(birth) = 5;
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
这里有一个小小的问题当目前月是12月。你不能仅仅把月份加1(12月)然后查找在13月份出生的动物,因为没有这个月。相反,你应该查找在1月份出生的动物。
你可以写一个查询不用关心当前月份是什么,因此你不用使用指定的月份了。DATE_ADD()使指定的日期增加固定的间隔。如果你使CURDATE()增加固定的一个月,然后使用MONTH()提取一个月,好么产生的月份就是你要找的出生日期:
mysql> SELECT name, birth from pet
-> where month(birth) = month(date_add(curdate(), interval 1 month));
另一种完成同样任务的作法是把当前月用取模函数(MOD)包裹之后如果当前是12月取模之后的值是0在加1:
mysql> SELECT name, birth from pet where month(birth) = mod(month(curdate()), 12) + 1;
+--------+------------+
| name | birth |
+--------+------------+
| Chirpy | 1998-09-11 |
+--------+------------+