日期计算
MySQL提供了一些函数,可用于对日期执行计算,例如,计算年龄或提取部分日期。
要确定您的每只宠物几岁,请使用该TIMESTAMPDIFF()
功能。它的参数是要表示结果的单位,以及两个日期之间的差值。以下查询为每只宠物显示出生日期,当前日期和年龄(以岁为单位)。一个别名( ge
)是用来制造最终输出列标签更有意义。
mysql>SELECT name , birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE())AS ageFROM 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 ageFROM petORDER 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 | +---------- +------------ +------------ +------ +
要使用 ge
而不是排序输出name
,只需使用其他ORDER BY
子句:
mysql>SELECT name , birth, CURDATE(), TIMESTAMPDIFF(YEAR,birth,CURDATE())AS ageFROM petORDER 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 ageFROM petWHERE death IS NOT NULLORDER 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()
是这里的适当功能。看看它是如何工作的,运行一个简单的查询同时显示的数值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月(月份5
)出生的动物:
mysql>SELECT name , birthFROM petWHERE MONTH(birth) = 5; +------- +------------ + | name | birth | +------- +------------ + | Buffy | 1989 -05 -13 | +------- +------------ +
如果当前月份为十二月,则情况会很复杂。您不能仅在月份号(12
)上加一个,并查找月份中出生的动物13
,因为没有这样的月份。相反,您寻找一月(月1
)出生的动物。
您可以编写查询以便无论当前月份是什么都可以使用,从而不必在特定月份使用该数字。DATE_ADD()
使您可以将时间间隔添加到给定的日期。如果您将的值加上一个月CURDATE()
,则使用提取月份部分MONTH()
,结果将生成要在其中寻找生日的月份:
mysql>SELECT name , birthFROM petWHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1MONTH ));
完成相同任务的另一种方法是1
在使用模函数(MOD
)将月份值包装0
为当前值之后,将其添加到当前月份之后的下个月12
:
mysql>SELECT name , birthFROM petWHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
MONTH()
返回介于1
和之间的数字12
。并MOD(something,12)
返回介于0
和之间的数字11
。因此加法必须在之后MOD()
,否则我们将从11月(11
)到1月(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' | +--------- +------ +---------------------------------------- +