MySQL 提供了几个可用于对日期进行计算的函数,例如,计算年龄或提取日期的一部分。
要确定每只宠物的年龄,请使用该
TIMESTAMPDIFF()
函数。它的参数是您想要表达结果的单位,以及要取差的两个日期。以下查询显示每只宠物的出生日期、当前日期和年龄(以年为单位)。别名( age
) 用于使最终输出列标签更有意义
。
mysql> SELECT name, birth, CURDATE(),
TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
FROM pet;
+----------+------------+------------+------+
| name | birth | CURDATE() | age |
+----------+------------+------------+------+
| Fluffy | 1993-02-04 | 2003-08-19 | 10 |
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 |
| Fang | 1990-08-27 | 2003-08-19 | 12 |
| Bowser | 1989-08-31 | 2003-08-19 | 13 |
| Chirpy | 1998-09-11 | 2003-08-19 | 4 |
| Whistler | 1997-12-09 | 2003-08-19 | 5 |
| Slim | 1996-04-29 | 2003-08-19 | 7 |
| Puffball | 1999-03-30 | 2003-08-19 | 4 |
+----------+------------+------------+------+
查询有效,但如果行以某种顺序显示,则可以更轻松地扫描结果。这可以通过添加一个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 | 2003-08-19 | 13 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 |
| Chirpy | 1998-09-11 | 2003-08-19 | 4 |
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Fang | 1990-08-27 | 2003-08-19 | 12 |
| Fluffy | 1993-02-04 | 2003-08-19 | 10 |
| Puffball | 1999-03-30 | 2003-08-19 | 4 |
| Slim | 1996-04-29 | 2003-08-19 | 7 |
| Whistler | 1997-12-09 | 2003-08-19 | 5 |
+----------+------------+------------+------+
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 | 2003-08-19 | 4 |
| Puffball | 1999-03-30 | 2003-08-19 | 4 |
| Whistler | 1997-12-09 | 2003-08-19 | 5 |
| Slim | 1996-04-29 | 2003-08-19 | 7 |
| Claws | 1994-03-17 | 2003-08-19 | 9 |
| Fluffy | 1993-02-04 | 2003-08-19 | 10 |
| Fang | 1990-08-27 | 2003-08-19 | 12 |
| Bowser | 1989-08-31 | 2003-08-19 | 13 |
| Buffy | 1989-05-13 | 2003-08-19 | 14 |
+----------+------------+------------+------+
类似的查询可用于确定已死亡动物的死亡年龄。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
rather than death <> NULL
because
NULL
是一个特殊值,不能使用通常的比较运算符进行比较。这将在后面讨论。请参阅第 3.3.4.6 节,“使用 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
,您可以像这样查找五月(月5
)出生的动物:
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
如果当前月份是 12 月,就会出现一个小问题。您不能只在月份编号 ( 12
) 上加一并寻找月份出生的动物
13
,因为没有这样的月份。相反,您寻找出生在一月(月
1
)的动物。
您可以编写查询,使其无论当前月份如何都有效,这样您就不必使用特定月份的数字。
DATE_ADD()
使您能够将时间间隔添加到给定日期。如果您将月份添加到 的值CURDATE()
,然后使用 提取月份部分MONTH()
,结果将生成查找生日的月份:
mysql> SELECT name, birth FROM pet
WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
完成相同任务的另一种方法是
在使用取模函数 ( ) 将月份值包装为当前月份后,添加1
以获取当前月份之后的下一个月:
MOD
0
12
mysql> SELECT name, birth FROM pet
WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
MONTH()
返回一个介于1
和之间的数字12
。并
MOD(something,12)
返回一个介于0
和之间的数字11
。所以加法必须在 之后
MOD()
,否则我们会从 November ( 11
) 到 January ( 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' |
+---------+------+----------------------------------------+