MySQL

1. TIMESTAMPDIFF() - 返回两个日期或日期表达式的差

语法：

TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)

返回datetime_expr2 - datetime_expr1, datetime_expr2和datetime_expr1是日期或日期时间表达式。一个表达式datetime_expr1可能是日期而另一个datetime_expr2可能是日期时间，此时前一个日期就会被当做日期时间处理，时间部分取值为'00:00:00'。返回结果为一个整数，其单位由unit指定。unit的合法取值如下：MICROSECOND(microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or Year。

计算年龄

mysql> select name, birth, curdate(), timestampdiff(year, birth, curdate()) as age from pet;+----------+------------+------------+------+| name     | birth      | curdate()  | age  |+----------+------------+------------+------+| Whistler | 0000-00-00 | 2017-04-09 | NULL || Puffball | 1989-08-31 | 2017-04-09 |   27 || Fluffy   | 1993-02-04 | 2017-04-09 |   24 || Claws    | 1994-03-17 | 2017-04-09 |   23 || Buffy    | 1989-05-13 | 2017-04-09 |   27 || Fang     | 1990-08-27 | 2017-04-09 |   26 || Bowser   | 1979-08-31 | 2017-04-09 |   37 || Chirpy   | 1998-09-11 | 2017-04-09 |   18 || Slim     | 1996-04-29 | 2017-04-09 |   20 |+----------+------------+------------+------+9 rows in set (0.00 sec)mysql>

使用order by name子句将结果排序

mysql> select name, birth, curdate(), timestampdiff(year, birth, curdate()) as age from pet order by name;+----------+------------+------------+------+| name     | birth      | curdate()  | age  |+----------+------------+------------+------+| Bowser   | 1979-08-31 | 2017-04-09 |   37 || Buffy    | 1989-05-13 | 2017-04-09 |   27 || Chirpy   | 1998-09-11 | 2017-04-09 |   18 || Claws    | 1994-03-17 | 2017-04-09 |   23 || Fang     | 1990-08-27 | 2017-04-09 |   26 || Fluffy   | 1993-02-04 | 2017-04-09 |   24 || Puffball | 1989-08-31 | 2017-04-09 |   27 || Slim     | 1996-04-29 | 2017-04-09 |   20 || Whistler | 0000-00-00 | 2017-04-09 | NULL |+----------+------------+------------+------+9 rows in set (0.00 sec)mysql>

按年龄排序，使用另一个order by子句

mysql> select name, birth, curdate(), timestampdiff(year, birth, curdate()) as age from pet order by age;+----------+------------+------------+------+| name     | birth      | curdate()  | age  |+----------+------------+------------+------+| Whistler | 0000-00-00 | 2017-04-09 | NULL || Chirpy   | 1998-09-11 | 2017-04-09 |   18 || Slim     | 1996-04-29 | 2017-04-09 |   20 || Claws    | 1994-03-17 | 2017-04-09 |   23 || Fluffy   | 1993-02-04 | 2017-04-09 |   24 || Fang     | 1990-08-27 | 2017-04-09 |   26 || Puffball | 1989-08-31 | 2017-04-09 |   27 || Buffy    | 1989-05-13 | 2017-04-09 |   27 || Bowser   | 1979-08-31 | 2017-04-09 |   37 |+----------+------------+------------+------+9 rows in set (0.00 sec)mysql>

查询已死的动物死时的年龄

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 | 1979-08-31 | 1995-07-29 |   15 |+--------+------------+------------+------+1 row in set (0.00 sec)mysql>

语句中使用death is not null而不是death <> null，是因为null是一个特殊值，不能用常用的比较操作。

2. 取出日期时间中的某一部分的值，可用的函数有year()，month()，dayofmonth()。

mysql> select name, birth, month(birth) from pet;+----------+------------+--------------+| name     | birth      | month(birth) |+----------+------------+--------------+| Whistler | 0000-00-00 |            0 || Puffball | 1989-08-31 |            8 || Fluffy   | 1993-02-04 |            2 || Claws    | 1994-03-17 |            3 || Buffy    | 1989-05-13 |            5 || Fang     | 1990-08-27 |            8 || Bowser   | 1979-08-31 |            8 || Chirpy   | 1998-09-11 |            9 || Slim     | 1996-04-29 |            4 |+----------+------------+--------------+9 rows in set (0.00 sec)mysql>

查询下个月过生日的宠物（假设当前是4月份）

mysql> select name, birth from pet where month(birth) = 5;+-------+------------+| name  | birth      |+-------+------------+| Buffy | 1989-05-13 |+-------+------------+1 row in set (0.00 sec)mysql>

如果当前月份是12月的话，则不能简单的给月份数字加1，因为12月的下个月是1月份。

我们可以写一个query使得无论当前月份是几月，查询语句都可以正确执行。DATE_ADD()函数可以让我们给一个指定日期加时间间隔，即可以获取当前日期(CURDATE())的一个月后的日期，然后再用month()获取月份数字，产生的结果就是下一月份的月份数字。

mysql> select name, birth from pet where month(birth) = month(date_add(curdate(), interval 1 month));+-------+------------+| name  | birth      |+-------+------------+| Buffy | 1989-05-13 |+-------+------------+1 row in set (0.00 sec)mysql>

另一个可以完成同样任务的方法是使用MOD()函数将月份数字设为0，如果当前月份值是12，然后再给得到的月份值加1：

mysql> select name, birth from pet where month(birth) = mod(month(curdate()), 12) + 1;+-------+------------+| name  | birth      |+-------+------------+| Buffy | 1989-05-13 |+-------+------------+1 row in set (0.00 sec)mysql>