12.7 Date and Time Functions日期和时间函数

This section describes the functions that can be used to manipulate temporal values. 本节描述可用于操作时间值的函数。See Section 11.2, “Date and Time Data Types”, for a description of the range of values each date and time type has and the valid formats in which values may be specified.请参阅第11.2节，“日期和时间数据类型”，以了解每个日期和时间类型的值范围以及指定值的有效格式。

Table 12.11 Date and Time Functions日期和时间函数

Name名称Description描述
ADDDATE()Add time values (intervals) to a date value将时间值（间隔）添加到日期值
ADDTIME()Add time添加时间
CONVERT_TZ()Convert from one time zone to another从一个时区转换到另一个时区
CURDATE()Return the current date返回当前日期
CURRENT_DATE(), CURRENT_DATESynonyms for CURDATE()CURDATE()的同义词
CURRENT_TIME(), CURRENT_TIMESynonyms for CURTIME()CURTIME()的同义词
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMPSynonyms for NOW()NOW()的同义词
CURTIME()Return the current time返回当前时间
DATE()Extract the date part of a date or datetime expression提取日期或日期时间表达式的日期部分
DATE_ADD()Add time values (intervals) to a date value将时间值（间隔）添加到日期值
DATE_FORMAT()Format date as specified按规定设置日期格式
DATE_SUB()Subtract a time value (interval) from a date从日期中减去时间值（间隔）
DATEDIFF()Subtract two dates两个日期相减
DAY()Synonym for DAYOFMONTH()DAYOFMONTH()的同义词
DAYNAME()Return the name of the weekday返回工作日的名称
DAYOFMONTH()Return the day of the month (0-31)返回月日（0-31)
DAYOFWEEK()Return the weekday index of the argument返回参数的工作日索引
DAYOFYEAR()Return the day of the year (1-366)返回一年中的某一天（1-366）
EXTRACT()Extract part of a date提取日期的一部分
FROM_DAYS()Convert a day number to a date将日数转换为日期
FROM_UNIXTIME()Format Unix timestamp as a date将Unix时间戳格式化为日期
GET_FORMAT()Return a date format string返回日期格式字符串
HOUR()Extract the hour提取小时
LAST_DAYReturn the last day of the month for the argument返回当月最后一天的参数
LOCALTIME(), LOCALTIMESynonym for NOW()NOW()的同义词
LOCALTIMESTAMP, LOCALTIMESTAMP()Synonym for NOW()NOW()的同义词
MAKEDATE()Create a date from the year and day of year从一年中的某一天创建一个日期
MAKETIME()Create time from hour, minute, second从小时、分钟、秒创建时间
MICROSECOND()Return the microseconds from argument返回参数的微秒数
MINUTE()Return the minute from the argument返回参数的分钟数
MONTH()Return the month from the date passed返回从过去日期算起的月份
MONTHNAME()Return the name of the month返回月份名称
NOW()Return the current date and time返回当前日期和时间
PERIOD_ADD()Add a period to a year-month将时段添加到年-月
PERIOD_DIFF()Return the number of months between periods返回时段之间的月数
QUARTER()Return the quarter from a date argument从日期参数返回季度
SEC_TO_TIME()Converts seconds to 'hh:mm:ss' format将秒转换为“hh:mm:ss”格式
SECOND()Return the second (0-59)返回秒（0-59）
STR_TO_DATE()Convert a string to a date将字符串转换为日期
SUBDATE()Synonym for DATE_SUB() when invoked with three arguments当用三个参数调用时，是DATE_SUB()的同义词
SUBTIME()Subtract times减去时间
SYSDATE()Return the time at which the function executes返回函数执行的时间
TIME()Extract the time portion of the expression passed提取表达式传递的时间部分
TIME_FORMAT()Format as time格式为时间
TIME_TO_SEC()Return the argument converted to seconds返回转换为秒的参数
TIMEDIFF()Subtract time减去时间
TIMESTAMP()With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments对于单个参数，此函数返回日期或日期时间表达式；对于两个参数，参数之和
TIMESTAMPADD()Add an interval to a datetime expression向日期时间表达式添加间隔
TIMESTAMPDIFF()Subtract an interval from a datetime expression从datetime表达式中减去间隔
TO_DAYS()Return the date argument converted to days返回转换为天的日期参数
TO_SECONDS()Return the date or datetime argument converted to seconds since Year 0返回从0年起转换为秒的日期或日期时间参数
UNIX_TIMESTAMP()Return a Unix timestamp返回Unix时间戳
UTC_DATE()Return the current UTC date返回当前UTC日期
UTC_TIME()Return the current UTC time返回当前UTC时间
UTC_TIMESTAMP()Return the current UTC date and time返回当前UTC日期和时间
WEEK()Return the week number返回周数
WEEKDAY()Return the weekday index返回工作日索引
WEEKOFYEAR()Return the calendar week of the date (1-53)返回日期的日历周（1-53）
YEAR()Return the year返回年份
YEARWEEK()Return the year and week返回年和周

Here is an example that uses date functions. 下面是一个使用日期函数的示例。the following query selects all rows with a date_col value from within the last 30 days:以下查询选择最近30天内具有date_col值的所有行：

mysql> SELECT something FROM tbl_name
    -> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;

the query also selects rows with dates that lie in the future.查询还选择日期在未来的行。

Functions that expect date values usually accept datetime values and ignore the time part. 期望日期值的函数通常接受日期时间值并忽略时间部分。Functions that expect time values usually accept datetime values and ignore the date part.期望时间值的函数通常接受datetime值并忽略日期部分。

Functions that return the current date or time each are evaluated only once per query at the start of query execution. 每次返回当前日期或时间的函数在查询执行开始时每个查询只计算一次。This means that multiple references to a function such as NOW() within a single query always produce the same result. 这意味着在单个查询中对函数的多个引用（如NOW()）总是产生相同的结果。(For our purposes, a single query also includes a call to a stored program (stored routine, trigger, or event) and all subprograms called by that program.) （出于我们的目的，单个查询还包括对存储程序（存储例程、触发器或事件）的调用以及该程序调用的所有子程序。）This principle also applies to CURDATE(), CURTIME(), UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP(), and to any of their synonyms.这一原则也适用于CURDATE()、CURTIME()、UTC_DATE()、UTC_TIME()、UTC_TIMESTAMP()及其任何同义词。

the CURRENT_TIMESTAMP(), CURRENT_TIME(), CURRENT_DATE(), and FROM_UNIXTIME() functions return values in the current session time zone, which is available as the session value of the time_zone system variable. CURRENT_TIMESTAMP()、CURRENT_TIME()、CURRENT_DATE()和FROM_UNIXTIME()函数返回当前会话时区中的值，该值可用作time_zone系统变量的会话值。In addition, UNIX_TIMESTAMP() assumes that its argument is a datetime value in the session time zone. 此外，UNIX_TIMESTAMP()假定其参数是会话时区中的日期时间值。See Section 5.1.15, “MySQL Server Time Zone Support”.请参阅第5.1.15节，“MySQL服务器时区支持”。

Some date functions can be used with “zero” dates or incomplete dates such as '2001-11-00', whereas others cannot. 一些日期函数可以与“零”日期或不完整日期（如'2001-11-00'）一起使用，而其他函数则不能。Functions that extract parts of dates typically work with incomplete dates and thus can return 0 when you might otherwise expect a nonzero value. 提取部分日期的函数通常处理不完整的日期，因此当您可能期望非零值时，可以返回0。For example:例如：

mysql> SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');
        -> 0, 0

Other functions expect complete dates and return NULL for incomplete dates. 其他函数需要完整的日期，不完整的日期返回NULL。These include functions that perform date arithmetic or that map parts of dates to names. 这些函数包括执行日期算术或将部分日期映射到名称的函数。For example:例如：

mysql> SELECT DATE_ADD('2006-05-00',INTERVAL 1 DAY);
        -> NULL
mysql> SELECT DAYNAME('2006-05-00');
        -> NULL

Several functions are strict when passed a DATE() function value as their argument and reject incomplete dates with a day part of zero: CONVERT_TZ(), DATE_ADD(), DATE_SUB(), DAYOFYEAR(), TIMESTAMPDIFF(), TO_DAYS(), TO_SECONDS(), WEEK(), WEEKDAY(), WEEKOFYEAR(), YEARWEEK().有几个函数在传递DATE()函数值作为参数时是严格的，并拒绝日期部分为零的不完整日期：CONVERT_TZ()、DATE_ADD()、DATE_SUB()、DAYOFYEAR()、TIMESTAMPDIFF()、TO_DAYS()、TO_SECONDS()、WEEK()、WEEKDAY()、WEEKOFYEAR()、YEARWEEK()。

Fractional seconds for TIME, DATETIME, and TIMESTAMP values are supported, with up to microsecond precision. 支持TIME、DATETIME和TIMESTAMP值的分秒，精度高达微秒。Functions that take temporal arguments accept values with fractional seconds. 采用时态参数的函数接受带小数秒的值。Return values from temporal functions include fractional seconds as appropriate.时间函数的返回值包括适当的小数秒。