Date Calculations

MySQL提供了一些可以用于日期计算的函数,例如,计算年龄或日期的某一部分.

使用函数TIMESTAMPDIFF()来获得你每个宠物的年龄,函数返回其两个参数的差值.对于每个宠物,函数的两个参数分别为出生日期,当前日期.age的使用让最后的输出结果更有意义.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT name, birth, CURDATE(),
-> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
-> FROM pet;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Fluffy | 1993-02-04 | 2003-08-19 | 21 |
| Claws | 1994-03-17 | 2003-08-19 | 20 |
| Buffy | 1989-05-13 | 2003-08-19 | 25 |
| Fang | 1990-08-27 | 2003-08-19 | 24 |
| Bowser | 1989-08-31 | 2003-08-19 | 25 |
| Chirpy | 1998-09-11 | 2003-08-19 | 16 |
| Whistler | 1997-12-09 | 2003-08-19 | 17 |
| Slim | 1996-04-29 | 2003-08-19 | 18 |
| Puffball | 1999-03-30 | 2003-08-19 | 15 |
+----------+------------+------------+------+

查询实现了,如果行按照一定的顺序排列,结果会更容易浏览.可以通过添加ORDER BY name来让输出结果按名字排序:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 | 25 |
| Buffy | 1989-05-13 | 2003-08-19 | 25 |
| Chirpy | 1998-09-11 | 2003-08-19 | 16 |
| Claws | 1994-03-17 | 2003-08-19 | 20 |
| Fang | 1990-08-27 | 2003-08-19 | 24 |
| Fluffy | 1993-02-04 | 2003-08-19 | 21 |
| Puffball | 1999-03-30 | 2003-08-19 | 15 |
| Slim | 1996-04-29 | 2003-08-19 | 18 |
| Whistler | 1997-12-09 | 2003-08-19 | 17 |
+----------+------------+------------+------+

一个相似的查询可以用来获得宠物死亡时的年龄,通过检查它们death的值是否为NULL,对于那些值不为NULL的,计算它们deathbirth之间的差值:

1
2
3
4
5
6
7
8
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()在这里是一个合适的函数,使用查询同时获取birthMONTH(birth)的值,可以展示它是如何使用的:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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,你可以这样查询在五月出生的动物:

1
2
3
4
5
6
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+

如果现在是十二月,情况可能会有些小复杂,你不能只是将月份的值加1,那样的话会查找出生在月份值为13的宠物,显然没有这样一个月份.相反,你应该查找出生在一月份的宠物.

你可以这样查询,以至于无论当前是什么月份,都可以被很好地实现.这样就不用为特定的月份使用特定的值了,DATA_ADD()可以让你实现对给定的日期添加时间间隔.如果你向CURDATE()添加一个月份,然后使用MONTH()确定日期的月份,也就是生日所在的月份:

1
2
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

另外一种实现上述查询的方法是,先使用取余函数MOD,然后再给月份的值加1:

1
2
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

MONTH()返回了一个介于112之间的数字.MOD(something, 12)返回了一个介于011之间的数字,所以必须在MOD()后加1,否则的话我们会从十一月11直接到一月1.

英文原文:

Date Calculations