6.4　日期和时间函数

日期和时间函数主要用来处理日期和时间值，一般的日期函数除了使用DATE类型的参数外，也可以使用DATETIME或者TIMESTAMP类型的参数，但会忽略这些值的时间部分。相同的，以TIME类型值为参数的函数，可以接受TIMESTAMP类型的参数，但会忽略日期部分，许多日期函数可以同时接受数字和字符串类型的两种参数，本节将介绍各种日期和时间函数的功能和用法。

6.4.1　获取当前日期的函数和获取当前时间的函数

CURDATE()和CURRENT_DATE()函数的作用相同，将当前日期按照‘YYYY-MM-DD’或YYYYMMDD格式的值返回，具体格式根据函数在字符串或是数字语境中而定。

【例6.53】使用日期函数获取系统当前日期，输入语句如下：

可以看到，两个函数作用相同，都返回了相同的系统当前日期，“CURDATE()+0”将当前日期值转换为数值型。

CURTIME()和CURRENT_TIME()函数的作用相同，将当前时间以‘HH:MM:SS’或HHMMSS的格式返回，具体格式根据函数在字符串或是数字语境中而定。

【例6.54】使用时间函数获取系统当前时间，输入语句如下：

可以看到，两个函数的作用相同，都返回了相同的系统当前时间，“CURTIME ()+0”将当前时间值转换为数值型。

6.4.2　获取当前日期和时间的函数

CURRENT_TIMESTAMP()、LOCALTIME()、NOW()和SYSDATE() 4个函数的作用相同，均返回当前日期和时间值，格式为‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS，具体格式根据函数在字符串或数字语境中而定。

【例6.55】使用日期时间函数获取当前系统日期和时间，输入语句如下：

可以看到，4个函数返回的结果是相同的。

6.4.3　UNIX时间戳函数

UNIX_TIMESTAMP(date)若无参数调用，则返回一个UNIX时间戳（‘1970-01-01 00:00:00’GMT之后的秒数）作为无符号整数。其中，GMT（Green wich mean time）为格林尼治标准时间。若用date来调用UNIX_TIMESTAMP()，它会将参数值以‘1970-01-01 00:00:00’GMT后的秒数的形式返回。date可以是一个DATE字符串、DATETIME字符串、TIMESTAMP或一个当地时间的YYMMDD或YYYYMMDD格式的数字。

【例6.56】使用UNIX_TIMESTAMP函数返回UNIX格式的时间戳，输入语句如下：

FROM_UNIXTIME(date)函数把UNIX时间戳转换为普通格式的时间，与UNIX_TIMESTAMP (date)函数互为反函数。

【例6.57】使用FROM_UNIXTIME函数将UNIX时间戳转换为普通格式时间，输入语句如下：

可以看到，FROM_UNIXTIME('1541844424')与【例6.56】中UNIX_TIMESTAMP(NOW())的结果正好相反，即两个函数互为反函数。

6.4.4　返回UTC日期的函数和返回UTC时间的函数

UTC_DATE()函数返回当前UTC（世界标准时间）日期值，其格式为‘YYYY-MM-DD’或YYYYMMDD，具体格式取决于函数是否用在字符串或数字语境中。

【例6.58】使用UTC_DATE()函数返回当前UTC日期值，输入语句如下：

UTC_DATE()函数返回值为当前时区的日期值。

UTC_TIME()返回当前UTC时间值，其格式为‘HH:MM:SS’或HHMMSS，具体格式取决于函数是否用在字符串或数字语境中。

【例6.59】使用UTC_TIME()函数返回当前UTC时间值，输入语句如下：

UTC_TIME()返回当前时区的时间值。

6.4.5　获取月份的函数MONTH(date)和MONTHNAME(date)

MONTH(date)函数返回date对应的月份，范围值为1~12。

【例6.60】使用MONTH()函数返回指定日期中的月份，输入语句如下：

MONTHNAME(date)函数返回日期date对应月份的英文全名。

【例6.61】使用MONTHNAME()函数返回指定日期中月份的名称，输入语句如下：

6.4.6　获取星期的函数DAYNAME(d)、DAYOFWEEK(d)和WEEKDAY(d)

DAYNAME(d)函数返回d对应的工作日的英文名称，例如Sunday、Monday等。

【例6.62】使用DAYNAME()函数返回指定日期的工作日名称，输入语句如下：

可以看到，2018年10月10日是星期三，因此返回结果为Wednesday。

DAYOFWEEK(d)函数返回d对应的一周中的索引（位置，1表示周日，2表示周一，...，7表示周六）。

【例6.63】使用DAYOFWEEK()函数返回日期对应的周索引，输入语句如下：

由【例6.63】可知，2018年10月10日为周三，因此返回其对应的索引值，结果为4。

WEEKDAY(d)返回d对应的工作日索引：0表示周一，1表示周二，...，6表示周日。

【例6.64】使用WEEKDAY()函数返回日期对应的工作日索引，输入语句如下：

可以看到，WEEKDAY()和DAYOFWEEK()函数都是返回指定日期在某一周内的位置，只是索引编号不同。

6.4.7　获取星期数的函数WEEK(d)和WEEKOFYEAR(d)

WEEK(d)计算日期d是一年中的第几周。WEEK()的双参数形式允许指定该星期是否起始于周日或周一，以及返回值的范围是否为0~53或1~53。若Mode参数被省略，则使用default_week_format系统自变量的值，可参考表6.1。

【例6.65】使用WEEK()函数查询指定日期是一年中的第几周，输入语句如下：

可以看到，WEEK('2018-02-20')使用一个参数，其第二个参数为default_week_format默认值，MySQL中该值默认为0，指定一周的第一天为周日，因此和WEEK('2018-02-20',0)返回结果相同；WEEK('2018-02-20',1)中第二个参数为1，指定一周的第一天为周一，返回值为8。可以看到，第二个参数不同，返回的结果也不同，使用不同的参数的原因是不同地区和国家的习惯不同，每周的第一天并不相同。

WEEKOFYEAR(d)计算某天位于一年中的第几周，范围是1~53，相当于WEEK(d,3)。

【例6.66】使用WEEKOFYEAR()查询指定日期是一年中的第几周，输入语句如下：

可以看到，两个函数返回结果相同。

6.4.8　获取天数的函数DAYOFYEAR(d)和DAYOFMONTH(d)

DAYOFYEAR(d)函数返回d是一年中的第几天，范围是1~366。

【例6.67】使用DAYOFYEAR()函数返回指定日期在一年中的位置，输入语句如下：

1月份31天，再加上2月份的20天，因此返回结果为51。

DAYOFMONTH(d)函数返回d是一个月中的第几天，范围是1~31。

【例6.68】使用DAYOFMONTH()函数返回指定日期在一个月中的位置，输入语句如下：

结果显而易见。

6.4.9　获取年份、季度、小时、分钟和秒钟的函数

YEAR(date)返回date对应的年份，范围是1970~2069。

【例6.69】使用YEAR()函数返回指定日期对应的年份，输入语句如下：

QUARTER(date)返回date对应的一年中的季度值，范围是1~4。

【例6.70】使用QUARTER()函数返回指定日期对应的季度，输入语句如下：

MINUTE(time)返回time对应的分钟数，范围是0~59。

【例6.71】使用MINUTE()函数返回指定时间的分钟值，输入语句如下：

SECOND(time)返回time对应的秒数，范围是0~59。

【例6.72】使用SECOND()函数返回指定时间的秒值，输入语句如下：

6.4.10　获取日期的指定值的函数EXTRACT(type FROM date)

EXTRACT(type FROM date)函数所使用的时间间隔类型说明符与DATE_ADD()或DATE_SUB()的相同，但它从日期中提取一部分，而不是执行日期运算。

【例6.73】使用EXTRACT函数提取日期或者时间值，输入语句如下：

type值为YEAR时，只返回年值，结果为2018；type值为YEAR_MONTH时返回年与月份，结果为201807；type值为DAY_MINUTE时，返回日、小时和分钟值，结果为120102。

6.4.11　时间和秒钟转换的函数

TIME_TO_SEC(time)返回已转化为秒的time参数。转换公式为：小时*3600+分钟*60+秒。

【例6.74】使用TIME_TO_SEC函数将时间值转换为秒值，输入语句如下：

SEC_TO_TIME(seconds)返回被转化为小时、分钟和秒数的seconds参数值，其格式为‘HH:MM:SS’或HHMMSS，具体格式根据该函数是否用在字符串或数字语境中而定。

【例6.75】使用SEC_TO_TIME()函数将秒值转换为时间格式，输入语句如下：

可以看到，SEC_TO_TIME函数返回值加上0值之后变成了小数值；TIME_TO_SEC正好和SEC_TO_TIME互为反函数。

6.4.12　计算日期和时间的函数

计算日期和时间的函数有DATE_ADD()、ADDDATE()、DATE_SUB()、SUBDATE()、ADDTIME()、SUBTIME()和DATE_DIFF()。

在DATE_ADD(date,INTERVAL expr type)和DATE_SUB(date,INTERVAL expr type)中，date是一个DATETIME或DATE值，用来指定起始时间。expr是一个表达式，用来指定从起始日期添加或减去的时间间隔值。对于负值的时间间隔，expr可以以一个负号‘-’开头。type为关键词，指示了表达式被解释的方式。表6.2显示了type和expr参数的关系。

若date参数是一个DATE值，计算只会包括YEAR、MONTH和DAY部分（没有时间部分），其结果是一个DATE值；否则，结果将是一个DATETIME值。

DATE_ADD(date,INTERVAL expr type)和ADDDATE(date,INTERVAL expr type)两个函数的作用相同，执行日期的加运算。

【例6.76】使用DATE_ADD()和ADDDATE()函数执行日期加操作，输入语句如下：

由结果可以看到，DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 SECOND)和ADDDATE('2010-12-31 23:59:59', INTERVAL 1 SECOND)两个函数执行的结果是相同的，将时间增加1秒后返回，结果都为‘2011-01-01 00:00:00’；DATE_ADD('2010-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND)日期运算类型是MINUTE_SECOND，将指定时间增加1分1秒后返回，结果为‘2011-01-01 00:01:00’。

DATE_SUB(date,INTERVAL expr type)或者SUBDATE(date,INTERVAL expr type)两个函数的作用相同，执行日期的减运算。

【例6.77】使用DATE_SUB和SUBDATE函数执行日期减操作，输入语句如下：

由结果可以看到，DATE_SUB('2011-01-02', INTERVAL 31 DAY)和SUBDATE('2011-01-02', INTERVAL 31 DAY)两个函数执行的结果是相同的，将日期值减少31天后返回，结果都为“2010-12-02”；DATE_SUB('2011-01-01 00:01:00',INTERVAL '0 0:1:1' DAY_SECOND)函数将指定日期减少1天，时间减少1分1秒后返回，结果为“2010-12-31 23:59:59”。

ADDTIME(date,expr)函数将expr值添加到date，并返回修改后的值，date是一个日期或者日期时间表达式，而expr是一个时间表达式。

【例6.78】使用ADDTIME进行时间加操作，输入语句如下：

可以看到，将“2000-12-31 23:59:59”的时间部分值增加1小时1分钟1秒后的日期变为“2001-01-01 01:01:00”；“02:02:02”增加两小时后的时间为“04:02:02”。

SUBTIME(date,expr)函数将date减去expr值，并返回修改后的值。其中，date是一个日期或者日期时间表达式，而expr是一个时间表达式。

【例6.79】使用SUBTIME()函数执行时间减操作，输入语句如下：

可以看到，将“2000-12-31 23:59:59”的时间部分值减少1小时1分钟1秒后的日期变为“2000-12-31 22:58:58”； “02:02:02”减少两小时的时间为“00:02:02”。

DATEDIFF(date1,date2)返回起始时间date1和结束时间date2之间的天数。date1和date2为日期或日期时间表达式。计算中只用到这些值的日期部分。

【例6.80】使用DATEDIFF()函数计算两个日期之间的间隔天数，输入语句如下：

DATEDIFF()函数返回date1-date2后的值，因此DATEDIFF('2010-12-31 23:59:59','2010-12-30')返回值为1；DATEDIFF('2010-11-30 23:59:59','2010-12-31')返回值为-31。

6.4.13　将日期和时间格式化的函数

DATE_FORMAT(date,format)根据format指定的格式显示date值。主要format格式如表6.3所示。

【例6.81】使用DATE_FORMAT()函数格式化输出日期和时间值，输入语句如下：

可以看到“1997-10-04 22:23:00”分别按照不同参数转换成了不同格式的日期值和时间值。

TIME_FORMAT(time,format)根据表达式format的要求显示时间time。表达式format指定了显示的格式。因为TIME_FORMAT(time,format)只处理时间，所以format只使用时间格式。

【例6.82】使用TIME_FORMAT()函数格式化输入时间值，输入语句如下：

TIME_FORMAT只处理时间值，可以看到，“16:00:00”按照不同的参数转换为不同格式的时间值。

GET_FORMAT(val_type, format_type)返回日期时间字符串的显示格式，val_type表示日期数据类型，包括DATE、DATETIME和TIME；format_type表示格式化显示类型，包括EUR、INTERVAL、ISO、JIS、USA。GET_FORMAT根据两个值类型组合返回的字符串显示格式如表6.4所示。

【例6.83】使用GET_FORMAT()函数显示不同格式化类型下的格式字符串，输入语句如下：

可以看到，不同类型的格式化字符串并不相同。

【例6.84】在DATE_FORMAT()函数中，使用GET_FORMAT函数返回的显示格式字符串来显示指定的日期值，输入语句如下：

GET_FORMAT(DATE,'USA')返回的显示格式字符串为%m.%d.%Y，对照表6.3中的显示格式（%m以数字形式显示月份，%d以数字形式显示日，%Y以4位数字形式显示年），因此结果为10.05.2000。