计算日期

在mysql中有一种方法，可以计算当前日期和差值，比如根据用户的出生日期可以算出年龄。

mysql> select name, species,birth from pet order by species,birth desc;
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Buffy    | dog     | 1989-05-13 |
| Bowser   | dog     | 1979-08-31 |
| Puffball | hamsjd  | 1999-03-28 |
+----------+---------+------------+

查询所有竖线显示如下，那么这个年龄怎么知道在今天他们是多少岁呢

执行以下命令：

mysql> select name, birth,curdate(),(year(curdate())-year(birth))-(right(curdate(),5)<right(birth,5)) as age from pet;
+----------+------------+------------+------+
| name     | birth      | curdate()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2018-07-29 |   25 |
| Claws    | 1994-03-17 | 2018-07-29 |   24 |
| Buffy    | 1989-05-13 | 2018-07-29 |   29 |
| Fang     | 1990-08-27 | 2018-07-29 |   27 |
| Bowser   | 1979-08-31 | 2018-07-29 |   38 |
| Chirpy   | 1998-09-11 | 2018-07-29 |   19 |
| Whistler | 1997-12-09 | 2018-07-29 |   20 |
| Puffball | 1999-03-28 | 2018-07-29 |   19 |
+----------+------------+------------+------+
9 rows in set (0.00 sec)

这个命令用到了内置函数，select name,birth, curdate()到这里我相信大家应该没什么问题主要输后面一句(year(curdate())-year(birth))-(right(curdate(),5)<right(birth,5))这一句我们逐个分析

year(curdate())-year(birth))是取当前年和出生年份差值

(right(curdate(),5)<right(birth,5))是比较当前月份和生日的月份差值，当前月份小于出生的月份则整体返回1

right(curdate(),5)表示取日期的最后5位，横线也算在内例如上面的就取 7-29

经过上面的介绍想必大家也都清楚，首先用年份差值计算大致的年龄，在根据日期的差值计算是否今年的生日已经过了

---

这是根据日期的简单计算