12.6 日期和时间功能
本节介绍可用于操纵时间值的功能。有关每种日期和时间类型具有的值范围以及可以指定值的有效格式的说明,请参见第 11.2 节“日期和时间数据类型”。
table12.10 日期和时间函数
Name | Description |
---|---|
ADDDATE() | 将时间值(间隔)添加到日期值 |
ADDTIME() | Add time |
CONVERT_TZ() | 从一个时区转换到另一个时区 |
CURDATE() | 返回当前日期 |
CURRENT_DATE(), CURRENT_DATE | CURDATE()的同义词 |
CURRENT_TIME(), CURRENT_TIME | CURTIME()的同义词 |
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP | NOW()的同义词 |
CURTIME() | 返回当前时间 |
DATE() | 提取日期或日期时间 table 达式的日期部分 |
DATE_ADD() | 将时间值(间隔)添加到日期值 |
DATE_FORMAT() | 指定格式日期 |
DATE_SUB() | 从日期中减去时间值(间隔) |
DATEDIFF() | 减去两个日期 |
DAY() | DAYOFMONTH()的同义词 |
DAYNAME() | 返回工作日的名称 |
DAYOFMONTH() | 返回月份中的一天(0-31) |
DAYOFWEEK() | 返回参数的工作日索引 |
DAYOFYEAR() | 返回一年中的某天(1-366) |
EXTRACT() | 提取部分日期 |
FROM_DAYS() | 将天数转换为日期 |
FROM_UNIXTIME() | 将 Unix 时间戳记格式化为日期 |
GET_FORMAT() | 返回日期格式字符串 |
HOUR() | 提取时间 |
LAST_DAY | 返回参数的月份的最后一天 |
LOCALTIME(), LOCALTIME | NOW()的同义词 |
LOCALTIMESTAMP, LOCALTIMESTAMP() | NOW()的同义词 |
MAKEDATE() | 从一年中的年月日创建日期 |
MAKETIME() | 从小时,分钟,秒创建时间 |
MICROSECOND() | 从参数返回微秒 |
MINUTE() | 返回参数的分钟 |
MONTH() | 返回经过日期的月份 |
MONTHNAME() | 返回月份名称 |
NOW() | 返回当前日期和时间 |
PERIOD_ADD() | 在一年的月份中添加一个期间 |
PERIOD_DIFF() | 返回期间之间的月数 |
QUARTER() | 从日期参数返回季度 |
SEC_TO_TIME() | 将秒转换为“ hh:mm:ss”格式 |
SECOND() | 返回第二个(0-59) |
STR_TO_DATE() | 将字符串转换为日期 |
SUBDATE() | 用三个参数调用时 DATE_SUB()的同义词 |
SUBTIME() | Subtract times |
SYSDATE() | 返回函数执行的时间 |
TIME() | 提取传递的 table 达式的时间部分 |
TIME_FORMAT() | 格式化为时间 |
TIME_TO_SEC() | 返回参数转换为秒 |
TIMEDIFF() | Subtract time |
TIMESTAMP() | 仅使用一个参数,此函数将返回日期或日期时间 table 达式。有两个参数,参数的总和 |
TIMESTAMPADD() | 向日期时间 table 达式添加间隔 |
TIMESTAMPDIFF() | 从日期时间 table 达式中减去一个间隔 |
TO_DAYS() | 返回日期参数转换为天 |
TO_SECONDS() | 返回从 Year 0 开始转换为秒的 date 或 datetime 参数 |
UNIX_TIMESTAMP() | 返回 Unix 时间戳 |
UTC_DATE() | 返回当前 UTC 日期 |
UTC_TIME() | 返回当前 UTC 时间 |
UTC_TIMESTAMP() | 返回当前 UTC 日期和时间 |
WEEK() | 返回星期数 |
WEEKDAY() | 返回工作日索引 |
WEEKOFYEAR() | 返回日期的 calendar 周(1-53) |
YEAR() | 返回年份 |
YEARWEEK() | 返回年和周 |
这是使用日期函数的示例。以下查询从过去 30 天内选择所有带有* date_col
*值的行:
mysql> SELECT something FROM tbl_name
-> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
该查询还会选择日期在将来的行。
期望日期值的函数通常接受 datetime 值,而忽略时间部分。期望时间值的函数通常接受 datetime 值,而忽略日期部分。
在查询开始执行时,每个查询仅返回一次返回当前日期或时间的函数。这意味着在单个查询中对诸如NOW()之类的函数的多个引用始终会产生相同的结果。 (出于我们的目的,单个查询还包括对存储程序(存储的例程,触发器或事件)的调用以及该程序调用的所有子程序。)此原理也适用于CURDATE(),CURTIME(),UTC_DATE(),UTC_TIME(),UTC_TIMESTAMP()和他们的任何同义词。
CURRENT_TIMESTAMP(),CURRENT_TIME(),CURRENT_DATE()和FROM_UNIXTIME()函数返回当前会话时区中的值,这些值可用作time_zone系统变量的会话值。此外,UNIX_TIMESTAMP()假定其参数是会话时区中的 datetime 值。参见第 5.1.13 节“ MySQL 服务器时区支持”。
某些日期函数可以用于“零”日期或不完整的日期,例如'2001-11-00'
,而其他一些则不能。提取日期部分的函数通常使用不完整的日期,因此当您可能期望非零值时,它们可以返回 0.例如:
mysql> SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');
-> 0, 0
其他函数需要完整的日期,不完整的日期返回NULL
。这些功能包括执行日期算术或将部分日期 Map 到名称的函数。例如:
mysql> SELECT DATE_ADD('2006-05-00',INTERVAL 1 DAY);
-> NULL
mysql> SELECT DAYNAME('2006-05-00');
-> NULL
几个函数在传递DATE()函数值作为参数时是严格的,并且拒绝不完整的日期(其天部分为零):CONVERT_TZ(),DATE_ADD(),DATE_SUB(),DAYOFYEAR(),TIMESTAMPDIFF(),TO_DAYS(),TO_SECONDS(),WEEK(),WEEKDAY(),WEEKOFYEAR(),YEARWEEK()。
支持TIME
,DATETIME
和TIMESTAMP
值的小数秒,精度高达微秒。带有时间参数的函数接受小数秒后的值。时间函数的返回值包括适当的小数秒。
ADDDATE(日期,INTERVALtable 示单位), ADDDATE(expr,days)
当使用第二个参数的INTERVAL
形式调用时,ADDDATE()是DATE_ADD()的同义词。相关函数SUBDATE()是DATE_SUB()的同义词。有关INTERVAL
* unit
*参数的信息,请参见Temporal Intervals。
mysql> SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
-> '2008-02-02'
mysql> SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
-> '2008-02-02'
当使用第二个参数的* days
形式调用时,MySQL 将其视为要添加到 expr
*的整数天。
mysql> SELECT ADDDATE('2008-01-02', 31);
-> '2008-02-02'
ADDTIME()将* expr2
加到 expr1
*并返回结果。 * expr1
是时间或日期时间 table 达式,而 expr2
*是时间 table 达式。
mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
-> '2008-01-02 01:01:01.000001'
mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
-> '03:00:01.999997'
CONVERT_TZ()将日期时间值* dt
从 from_tz
给定的时区转换为 to_tz
*给定的时区,并返回结果值。如第 5.1.13 节“ MySQL 服务器时区支持”中所述指定时区。如果参数无效,此函数返回NULL
。
从* from_tz
*转换为 UTC 时,如果该值超出TIMESTAMP类型支持的范围,则不会进行转换。 第 11.2.1 节“日期和时间数据类型语法”中描述了TIMESTAMP范围。
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');
-> '2004-01-01 13:00:00'
mysql> SELECT CONVERT_TZ('2004-01-01 12:00:00','+00:00','+10:00');
-> '2004-01-01 22:00:00'
Note
要使用诸如'MET'
或'Europe/Amsterdam'
之类的命名时区,必须正确设置时区 table。有关说明,请参见第 5.1.13 节“ MySQL 服务器时区支持”。
以'YYYY-MM-DD'
或* YYYYMMDD
*格式返回当前日期作为值,具体取决于该函数是在字符串环境中还是在数字环境中使用。
mysql> SELECT CURDATE();
-> '2008-06-13'
mysql> SELECT CURDATE() + 0;
-> 20080613
CURRENT_DATE和CURRENT_DATE()是CURDATE()的同义词。
CURRENT_TIME, CURRENT_TIME([fsp])
CURRENT_TIME和CURRENT_TIME()是CURTIME()的同义词。
CURRENT_TIMESTAMP, CURRENT_TIMESTAMP([fsp])
CURRENT_TIMESTAMP和CURRENT_TIMESTAMP()是NOW()的同义词。
以* 'hh:mm:ss'
或 hhmmss
*格式返回当前时间作为值,具体取决于该函数是在字符串上下文中还是在数字上下文中使用。该值以会话时区 table 示。
如果使用* fsp
*参数指定从 0 到 6 的小数秒精度,则返回值包括该位数的小数秒部分。
mysql> SELECT CURTIME();
-> '23:50:26'
mysql> SELECT CURTIME() + 0;
-> 235026.000000
提取日期或日期时间 table 达式* expr
*的日期部分。
mysql> SELECT DATE('2003-12-31 01:02:03');
-> '2003-12-31'
DATEDIFF()返回* expr1
- expr2
*,以从一个日期到另一个日期的天数 table 示。 * expr1
和 expr2
*是日期或日期和时间 table 达式。在计算中仅使用值的日期部分。
mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
-> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
-> -31
DATE_ADD(日期,INTERVALtable 示单位), DATE_SUB(日期,INTERVALtable 示单位)
这些函数执行日期算术。 * date
*参数指定开始日期或日期时间值。 * expr
*是一个 table 达式,指定要从开始日期添加或减去的间隔值。 * expr
*被评估为字符串;它可能以-
开头出现负间隔。 * unit
*是一个关键字,指示解释 table 达式的单位。
有关时间间隔语法的更多信息,包括* unit
说明符的完整列 table,每个 unit
*值的_ expr
*参数的预期形式以及时间算术中操作数解释的规则,请参阅Temporal Intervals。
返回值取决于参数:
为确保结果为DATETIME,可以使用CAST()将第一个参数转换为DATETIME。
mysql> SELECT DATE_ADD('2018-05-01',INTERVAL 1 DAY);
-> '2018-05-02'
mysql> SELECT DATE_SUB('2018-05-01',INTERVAL 1 YEAR);
-> '2017-05-01'
mysql> SELECT DATE_ADD('2020-12-31 23:59:59',
-> INTERVAL 1 SECOND);
-> '2021-01-01 00:00:00'
mysql> SELECT DATE_ADD('2018-12-31 23:59:59',
-> INTERVAL 1 DAY);
-> '2019-01-01 23:59:59'
mysql> SELECT DATE_ADD('2100-12-31 23:59:59',
-> INTERVAL '1:1' MINUTE_SECOND);
-> '2101-01-01 00:01:00'
mysql> SELECT DATE_SUB('2025-01-01 00:00:00',
-> INTERVAL '1 1:1:1' DAY_SECOND);
-> '2024-12-30 22:58:59'
mysql> SELECT DATE_ADD('1900-01-01 00:00:00',
-> INTERVAL '-1 10' DAY_HOUR);
-> '1899-12-30 14:00:00'
mysql> SELECT DATE_SUB('1998-01-02', INTERVAL 31 DAY);
-> '1997-12-02'
mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
-> INTERVAL '1.999999' SECOND_MICROSECOND);
-> '1993-01-01 00:00:01.000001'
根据* format
字符串格式化 date
*值。
下 table 中显示的说明符可以在* format
*字符串中使用。格式说明符之前必须有%
字符。这些说明符也适用于其他功能:STR_TO_DATE(),TIME_FORMAT(),UNIX_TIMESTAMP()。
Specifier | Description |
---|---|
%a | 工作日的缩写名称(Sun .. Sat ) |
%b | 月份缩写名称(Jan .. Dec ) |
%c | 月份,数字(0 .. 12 ) |
%D | 带英文后缀(每月0th ,1st ,2nd ,3rd …的月份) |
%d | 每月的某天,数字(00 .. 31 ) |
%e | 每月的某天,数字(0 .. 31 ) |
%f | 微秒(000000 .. 999999 ) |
%H | 小时(00 .. 23 ) |
%h | 小时(01 .. 12 ) |
%I | 小时(01 .. 12 ) |
%i | 分钟,数字(00 .. 59 ) |
%j | 一年中的某天(001 .. 366 ) |
%k | 小时(0 .. 23 ) |
%l | 小时(1 .. 12 ) |
%M | 月名称(January .. December ) |
%m | 月份,数字(00 .. 12 ) |
%p | AM 或PM |
%r | 时间 12 小时(* hh:mm:ss *,后跟AM 或PM ) |
%S | 秒(00 .. 59 ) |
%s | 秒(00 .. 59 ) |
%T | 时间 24 小时(* hh:mm:ss *) |
%U | 周(00 .. 53 ),其中星期日是一周的第一天; WEEK()模式 0 |
%u | 周(00 .. 53 ),其中星期一是一周的第一天; WEEK()模式 1 |
%V | 周(01 .. 53 ),其中星期日是一周的第一天; WEEK()模式 2;与%X 一起使用 |
%v | 周(01 .. 53 ),其中星期一是一周的第一天; WEEK()模式 3;与%x 一起使用 |
%W | 工作日名称(Sunday .. Saturday ) |
%w | 星期几(0 =星期日.. 6 =星期六) |
%X | 星期的年份,其中星期日是星期的第一天,数字,四位数;与%V 一起使用 |
%x | 一周的年份,其中星期一是一周的第一天,数字,四位数;与%v 一起使用 |
%Y | 年,数字,四位数 |
%y | 年,数字(两位数字) |
%% | Literals% 字符 |
%x | * x *,对于上面未列出的任何“ * x *” |
由于 MySQL 允许存储不完整的日期(例如'2014-00-00'
),因此月份和日期说明符的范围以零开头。
日和月的名称和缩写所使用的语言由lc_time_names系统变量(第 10.16 节“ MySQL Server 语言环境支持”)的值控制。
对于%U
,%u
,%V
和%v
的说明符,请参见WEEK()函数的描述以获取有关模式值的信息。该模式影响星期编号的发生方式。
DATE_FORMAT()返回一个字符串,该字符串具有character_set_connection和collation_connection给出的字符集和排序规则,以便它可以返回包含非 ASCII 字符的月份和工作日名称。
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
-> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
-> '%D %y %a %d %m %b %j');
-> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
-> '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
-> '00'
DATE_SUB(日期,INTERVALtable 示单位)
请参阅DATE_ADD()的说明。
DAY()是DAYOFMONTH()的同义词。
返回* date
*的工作日名称。名称使用的语言由lc_time_names系统变量(第 10.16 节“ MySQL Server 语言环境支持”)的值控制。
mysql> SELECT DAYNAME('2007-02-03');
-> 'Saturday'
返回* date
*的日期,范围为1
到31
,或者返回0
table 示日期(例如'0000-00-00'
或'2008-00-00'
)的日期为零。
mysql> SELECT DAYOFMONTH('2007-02-03');
-> 3
返回* date
*(1
=星期日,2
=星期一,…,7
=星期六)的工作日索引。这些索引值对应于 ODBC 标准。
mysql> SELECT DAYOFWEEK('2007-02-03');
-> 7
返回* date
*的年份,范围为1
到366
。
mysql> SELECT DAYOFYEAR('2007-02-03');
-> 34
EXTRACT()函数使用与DATE_ADD()或DATE_SUB()相同的* unit
指定符,但是从日期中提取部分而不是执行日期算术。有关 unit
*参数的信息,请参见Temporal Intervals。
mysql> SELECT EXTRACT(YEAR FROM '2019-07-02');
-> 2019
mysql> SELECT EXTRACT(YEAR_MONTH FROM '2019-07-02 01:02:03');
-> 201907
mysql> SELECT EXTRACT(DAY_MINUTE FROM '2019-07-02 01:02:03');
-> 20102
mysql> SELECT EXTRACT(MICROSECOND
-> FROM '2003-01-02 10:30:00.000123');
-> 123
给定日期数字* N
*,则返回DATE值。
mysql> SELECT FROM_DAYS(730669);
-> '2000-07-03'
旧日期请谨慎使用FROM_DAYS()。它不适用于公历(1582)出现之前的值。参见第 12.8 节“ MySQL 使用什么 calendar?”。
FROM_UNIXTIME(unix_timestamp[,format])
以'YYYY-MM-DD hh:mm:ss'
或* YYYYMMDDhhmmss
格式返回 unix_timestamp
*参数的 table 示形式,具体取决于该函数是在字符串还是在数字上下文中使用。 * unix_timestamp
*是一个内部时间戳记值,table 示自'1970-01-01 00:00:00'
UTC 以来的秒数,例如UNIX_TIMESTAMP()函数产生的时间。
返回值以会话时区 table 示。 (Client 可以按照第 5.1.13 节“ MySQL 服务器时区支持”中的说明设置会话时区。)* format
*字符串(如果提供)用于格式化结果,格式与DATE_FORMAT()函数的条目中所述相同。
mysql> SELECT FROM_UNIXTIME(1447430881);
-> '2015-11-13 10:08:01'
mysql> SELECT FROM_UNIXTIME(1447430881) + 0;
-> 20151113100801
mysql> SELECT FROM_UNIXTIME(1447430881,
-> '%Y %D %M %h:%i:%s %x');
-> '2015 13th November 10:08:01 2015'
Note
如果您使用UNIX_TIMESTAMP()和FROM_UNIXTIME()在非 UTC 时区的值和 Unix 时间戳值之间进行转换,则转换是有损耗的,因为在两个方向上 Map 都不是一对一的。有关详细信息,请参见UNIX_TIMESTAMP()函数的描述。
GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})
返回格式字符串。将此功能与DATE_FORMAT()和STR_TO_DATE()功能结合使用。
第一个和第二个参数的可能值会导致几个可能的格式字符串(有关所使用的说明符,请参见DATE_FORMAT()函数描述中的 table)。 ISO 格式是指 ISO 9075,而不是 ISO 8601.
Function Call | Result |
---|---|
GET_FORMAT(DATE,'USA') | '%m.%d.%Y' |
GET_FORMAT(DATE,'JIS') | '%Y-%m-%d' |
GET_FORMAT(DATE,'ISO') | '%Y-%m-%d' |
GET_FORMAT(DATE,'EUR') | '%d.%m.%Y' |
GET_FORMAT(DATE,'INTERNAL') | '%Y%m%d' |
GET_FORMAT(DATETIME,'USA') | '%Y-%m-%d %H.%i.%s' |
GET_FORMAT(DATETIME,'JIS') | '%Y-%m-%d %H:%i:%s' |
GET_FORMAT(DATETIME,'ISO') | '%Y-%m-%d %H:%i:%s' |
GET_FORMAT(DATETIME,'EUR') | '%Y-%m-%d %H.%i.%s' |
GET_FORMAT(DATETIME,'INTERNAL') | '%Y%m%d%H%i%s' |
GET_FORMAT(TIME,'USA') | '%h:%i:%s %p' |
GET_FORMAT(TIME,'JIS') | '%H:%i:%s' |
GET_FORMAT(TIME,'ISO') | '%H:%i:%s' |
GET_FORMAT(TIME,'EUR') | '%H.%i.%s' |
GET_FORMAT(TIME,'INTERNAL') | '%H%i%s' |
TIMESTAMP也可用作GET_FORMAT()的第一个参数,在这种情况下,该函数返回的值与DATETIME相同。
mysql> SELECT DATE_FORMAT('2003-10-03',GET_FORMAT(DATE,'EUR'));
-> '03.10.2003'
mysql> SELECT STR_TO_DATE('10.31.2003',GET_FORMAT(DATE,'USA'));
-> '2003-10-31'
返回* time
*的小时数。对于一天中的时间值,返回值的范围是0
到23
。但是,TIME值的范围实际上要大得多,因此HOUR
可以返回大于23
的值。
mysql> SELECT HOUR('10:05:03');
-> 10
mysql> SELECT HOUR('272:59:59');
-> 272
取得日期或日期时间值,并返回当月最后一天的相应值。如果参数无效,则返回NULL
。
mysql> SELECT LAST_DAY('2003-02-05');
-> '2003-02-28'
mysql> SELECT LAST_DAY('2004-02-05');
-> '2004-02-29'
mysql> SELECT LAST_DAY('2004-01-01 01:01:01');
-> '2004-01-31'
mysql> SELECT LAST_DAY('2003-03-32');
-> NULL
LOCALTIME和LOCALTIME()是NOW()的同义词。
LOCALTIMESTAMP, LOCALTIMESTAMP([fsp])
LOCALTIMESTAMP和LOCALTIMESTAMP()是NOW()的同义词。
返回给定日期和年值的日期。 * dayofyear
*必须大于 0 或结果为NULL
。
mysql> SELECT MAKEDATE(2011,31), MAKEDATE(2011,32);
-> '2011-01-31', '2011-02-01'
mysql> SELECT MAKEDATE(2011,365), MAKEDATE(2014,365);
-> '2011-12-31', '2014-12-31'
mysql> SELECT MAKEDATE(2011,0);
-> NULL
返回根据* hour
, minute
和 second
*参数计算出的时间值。
second
*参数可以包含小数部分。
mysql> SELECT MAKETIME(12,15,30);
-> '12:15:30'
从时间或日期时间 table 达式* expr
*返回微秒,作为从0
到999999
范围内的数字。
mysql> SELECT MICROSECOND('12:00:00.123456');
-> 123456
mysql> SELECT MICROSECOND('2019-12-31 23:59:59.000010');
-> 10
返回* time
*的分钟,范围为0
到59
。
mysql> SELECT MINUTE('2008-02-03 10:05:03');
-> 5
返回* date
*的月份,一月至十二月的范围为1
到12
,对于'0000-00-00'
或'2008-00-00'
之类的日期(零月份)返回0
。
mysql> SELECT MONTH('2008-02-03');
-> 2
返回* date
*的月份的全名。名称使用的语言由lc_time_names系统变量(第 10.16 节“ MySQL Server 语言环境支持”)的值控制。
mysql> SELECT MONTHNAME('2008-02-03');
-> 'February'
以'YYYY-MM-DD hh:mm:ss'
或* YYYYMMDDhhmmss
*格式返回当前日期和时间作为值,具体取决于该函数是在字符串上下文中还是在数字上下文中使用。该值以会话时区 table 示。
如果使用* fsp
*参数指定从 0 到 6 的小数秒精度,则返回值包括该位数的小数秒部分。
mysql> SELECT NOW();
-> '2007-12-15 23:50:26'
mysql> SELECT NOW() + 0;
-> 20071215235026.000000
NOW()返回一个恒定时间,该时间指示语句开始执行的时间。 (在已存储的函数或触发器中,NOW()返回函数或触发语句开始执行的时间。)这与SYSDATE()的行为不同,SYSDATE()返回其执行的确切时间。
mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW() | SLEEP(2) | NOW() |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 | 0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+
mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE() | SLEEP(2) | SYSDATE() |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 | 0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+
另外,SET TIMESTAMP
语句影响NOW()而不是SYSDATE()返回的值。这意味着二进制日志中的时间戳设置对SYSDATE()的调用没有影响。将时间戳设置为非零值会导致每次随后的NOW()调用都返回该值。将时间戳设置为零会取消此效果,因此NOW()再次返回当前日期和时间。
有关这两个功能之间差异的更多信息,请参见SYSDATE()的描述。
将* N
月添加到期间 P
(格式为 YYMM
或 YYYYMM
)。返回格式为 YYYYMM
*的值。
Note
期间参数* P
不是日期值。
mysql> SELECT PERIOD_ADD(200801,2);
-> 200803
返回周期* P1
和 P2
之间的月数。 * P1
和 P2
的格式应为 YYMM
或 YYYYMM
。请注意,期间参数 P1
和 P2
是不是日期值。
mysql> SELECT PERIOD_DIFF(200802,200703);
-> 11
返回* date
*的年度季度,范围为1
到4
。
mysql> SELECT QUARTER('2008-04-01');
-> 2
返回* time
*的第二个,范围0
到59
。
mysql> SELECT SECOND('10:05:03');
-> 3
返回seconds
*参数,并转换为小时,分钟和秒,作为TIME值。结果范围限制为TIME数据类型。如果参数对应于该范围之外的值,则会发生警告。
mysql> SELECT SEC_TO_TIME(2378);
-> '00:39:38'
mysql> SELECT SEC_TO_TIME(2378) + 0;
-> 3938
这是DATE_FORMAT()函数的反函数。它需要一个字符串* str
和一个格式字符串 format
。如果格式字符串同时包含日期和时间部分,则STR_TO_DATE()返回DATETIME值;如果字符串仅包含日期或时间部分,则STR_TO_DATE()返回DATE或TIME值。如果从 str
*中提取的日期,时间或日期时间值是非法的,则STR_TO_DATE()返回NULL
并产生警告。
服务器扫描* str
尝试与之匹配 format
*。格式字符串可以包含 Literals 字符和以%
开头的格式说明符。 * format
中的 Literals 字符必须与 str
*中的 Literals 匹配。 * format
中的格式说明符必须与 str
中的日期或时间部分匹配。有关可以在 format
*中使用的说明符,请参见DATE_FORMAT()功能描述。
mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
-> '2013-05-01'
mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
-> '2013-05-01'
扫描从* str
的开头开始,如果发现 format
*不匹配,则扫描失败。 * str
*末尾的多余字符将被忽略。
mysql> SELECT STR_TO_DATE('a09:30:17','a%h:%i:%s');
-> '09:30:17'
mysql> SELECT STR_TO_DATE('a09:30:17','%h:%i:%s');
-> NULL
mysql> SELECT STR_TO_DATE('09:30:17a','%h:%i:%s');
-> '09:30:17'
未指定的日期或时间部分的值为 0,因此* str
*中不完全指定的值会产生将部分或全部部分设置为 0 的结果:
mysql> SELECT STR_TO_DATE('abc','abc');
-> '0000-00-00'
mysql> SELECT STR_TO_DATE('9','%m');
-> '0000-09-00'
mysql> SELECT STR_TO_DATE('9','%s');
-> '00:00:09'
日期值部分的范围检查如第 11.2.2 节“ DATE,DATETIME 和 TIMESTAMP 类型”中所述。例如,这意味着允许“零”日期或部分值为 0 的日期,除非将 SQL 模式设置为不允许此类值。
mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
-> '0000-00-00'
mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
-> '2004-04-31'
如果启用了NO_ZERO_DATE SQL 模式,则不允许使用零日期。在这种情况下,STR_TO_DATE()返回NULL
并生成警告:
mysql> SET sql_mode = '';
mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('00/00/0000', '%m/%d/%Y') |
+---------------------------------------+
| 0000-00-00 |
+---------------------------------------+
mysql> SET sql_mode = 'NO_ZERO_DATE';
mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('00/00/0000', '%m/%d/%Y') |
+---------------------------------------+
| NULL |
+---------------------------------------+
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Warning
Code: 1411
Message: Incorrect datetime value: '00/00/0000' for function str_to_date
Note
您不能使用格式"%X%V"
将年-星期的字符串转换为日期,因为如果星期和月份越过边界,则年份和星期的组合不能唯一地标识年份和月份。要将年周转换为日期,还应该指定工作日:
mysql> SELECT STR_TO_DATE('200442 Monday', '%X%V %W');
-> '2004-10-18'
SUBDATE(日期,INTERVALtable 示单位), SUBDATE(expr,days)
当使用第二个参数的INTERVAL
形式调用时,SUBDATE()是DATE_SUB()的同义词。有关INTERVAL
* unit
*参数的信息,请参见有关DATE_ADD()的讨论。
mysql> SELECT DATE_SUB('2008-01-02', INTERVAL 31 DAY);
-> '2007-12-02'
mysql> SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
-> '2007-12-02'
第二种形式允许对* days
使用整数值。在这种情况下,它被解释为要从日期或日期时间 table 达式 expr
*中减去的天数。
mysql> SELECT SUBDATE('2008-01-02 12:00:00', 31);
-> '2007-12-02 12:00:00'
SUBTIME()返回* expr1
- expr2
以与 expr1
*相同的格式 table 示为值。 * expr1
是时间或日期时间 table 达式,而 expr2
*是时间 table 达式。
mysql> SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');
-> '2007-12-30 22:58:58.999997'
mysql> SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');
-> '-00:59:59.999999'
以'YYYY-MM-DD hh:mm:ss'
或* YYYYMMDDhhmmss
*格式返回当前日期和时间作为值,具体取决于该函数是在字符串上下文中还是在数字上下文中使用。
如果使用* fsp
*参数指定从 0 到 6 的小数秒精度,则返回值包括该位数的小数秒部分。
SYSDATE()返回执行时间。这与NOW()的行为不同,后者返回一个恒定时间,该时间指示语句开始执行的时间。 (在存储的函数或触发器中,NOW()返回函数或触发语句开始执行的时间。)
mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW() | SLEEP(2) | NOW() |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:36 | 0 | 2006-04-12 13:47:36 |
+---------------------+----------+---------------------+
mysql> SELECT SYSDATE(), SLEEP(2), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE() | SLEEP(2) | SYSDATE() |
+---------------------+----------+---------------------+
| 2006-04-12 13:47:44 | 0 | 2006-04-12 13:47:46 |
+---------------------+----------+---------------------+
此外,SET TIMESTAMP
语句影响NOW()而不是SYSDATE()返回的值。这意味着二进制日志中的时间戳设置对SYSDATE()的调用没有影响。
因为SYSDATE()即使在同一条语句内也可以返回不同的值,并且不受SET TIMESTAMP
的影响,所以它是不确定的,因此如果使用基于语句的二进制日志记录,则复制不安全。如果存在问题,则可以使用基于行的日志记录。
或者,您可以使用--sysdate-is-now选项使SYSDATE()成为NOW()的别名。如果在主站和从站上都使用了该选项,则此方法有效。
SYSDATE()的不确定性也意味着不能将索引用于评估引用它的 table 达式。
提取时间或日期时间 table 达式* expr
*的时间部分,并将其作为字符串返回。
此功能对于基于语句的复制不安全。如果在binlog_format设置为STATEMENT
时使用此功能,则会记录一条警告。
mysql> SELECT TIME('2003-12-31 01:02:03');
-> '01:02:03'
mysql> SELECT TIME('2003-12-31 01:02:03.000123');
-> '01:02:03.000123'
TIMEDIFF()返回* expr1
- expr2
*,以时间值 table 示。 * expr1
和 expr2
*是时间或日期和时间 table 达式,但两者必须是同一类型。
TIMEDIFF()
返回的结果限于TIME值所允许的范围。或者,您可以使用TIMESTAMPDIFF()和UNIX_TIMESTAMP()之一,它们都返回整数。
mysql> SELECT TIMEDIFF('2000:01:01 00:00:00',
-> '2000:01:01 00:00:00.000001');
-> '-00:00:00.000001'
mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001',
-> '2008-12-30 01:01:01.000002');
-> '46:58:57.999999'
TIMESTAMP(expr), TIMESTAMP(expr1,expr2)
仅使用一个参数,此函数将日期或日期时间 table 达式* expr
返回为日期时间值。它使用两个参数将时间 table 达式 expr2
添加到日期或日期时间 table 达式 expr1
*并将结果作为日期时间值返回。
mysql> SELECT TIMESTAMP('2003-12-31');
-> '2003-12-31 00:00:00'
mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');
-> '2004-01-01 00:00:00'
TIMESTAMPADD(unit,interval,datetime_expr)
将整数 table 达式* interval
添加到日期或日期时间 table 达式 datetime_expr
*。 * interval
的单位由 unit
*参数指定,应为以下值之一:MICROSECOND
(微秒),SECOND
,MINUTE
,HOUR
,DAY
,WEEK
,MONTH
,QUARTER
或YEAR
。
unit
*值可以使用所示的一个关键字或SQL_TSI_
作为前缀来指定。例如,DAY
和SQL_TSI_DAY
都是合法的。
mysql> SELECT TIMESTAMPADD(MINUTE,1,'2003-01-02');
-> '2003-01-02 00:01:00'
mysql> SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
-> '2003-01-09'
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
返回* datetime_expr2
- datetime_expr1
,其中 datetime_expr1
和 datetime_expr2
是日期或日期时间 table 达式。一个 table 达式可以是日期,另一个可以是日期时间。在必要时,将日期值视为具有时间部分'00:00:00'
的日期时间。结果的单位(整数)由 unit
*参数指定。 * unit
*的合法值与TIMESTAMPADD()函数的说明中列出的合法值相同。
mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
-> 3
mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
-> -1
mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');
-> 128885
Note
当使用 2 个参数调用时,此函数的 date 或 datetime 参数的 Sequences 与TIMESTAMP()函数使用的 Sequences 相反。
用法与DATE_FORMAT()函数类似,但是* format
*字符串可能仅包含小时,分钟,秒和微秒的格式说明符。其他说明符产生NULL
值或0
。
如果* time
*值中的小时部分大于23
,则%H
和%k
小时格式说明符会产生一个比通常范围0..23
大的值。其他小时格式说明符会产生以 12 为模的小时值。
mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
-> '100 100 04 04 4'
返回* time
*参数,转换为秒。
mysql> SELECT TIME_TO_SEC('22:23:00');
-> 80580
mysql> SELECT TIME_TO_SEC('00:39:38');
-> 2378
给定日期* date
*,返回天数(自 0 年起的天数)。
mysql> SELECT TO_DAYS(950501);
-> 728779
mysql> SELECT TO_DAYS('2007-10-07');
-> 733321
TO_DAYS()不适用于公历(1582)出现之前的值,因为它未考虑更改 calendar 时丢失的日期。对于 1582 年之前的日期(可能在其他地方的较晚年份),此功能的结果不可靠。有关详情,请参见第 12.8 节“ MySQL 使用什么 calendar?”。
请记住,MySQL 使用第 11.2 节“日期和时间数据类型”中的规则将日期中的两位数字年份值转换为四位数形式。例如,'2008-10-07'
和'08-10-07'
被视为相同的日期:
mysql> SELECT TO_DAYS('2008-10-07'), TO_DAYS('08-10-07');
-> 733687, 733687
在 MySQL 中,零日期定义为'0000-00-00'
,即使该日期本身被视为无效。这意味着,对于'0000-00-00'
和'0000-01-01'
,TO_DAYS()返回此处显示的值:
mysql> SELECT TO_DAYS('0000-00-00');
+-----------------------+
| to_days('0000-00-00') |
+-----------------------+
| NULL |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '0000-00-00' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TO_DAYS('0000-01-01');
+-----------------------+
| to_days('0000-01-01') |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
无论是否启用ALLOW_INVALID_DATES SQL Server 模式,都是如此。
给定日期或日期时间* expr
,返回自 0 年以来的秒数。如果 expr
*不是有效的日期或日期时间值,则返回NULL
。
mysql> SELECT TO_SECONDS(950501);
-> 62966505600
mysql> SELECT TO_SECONDS('2009-11-29');
-> 63426672000
mysql> SELECT TO_SECONDS('2009-11-29 13:43:32');
-> 63426721412
mysql> SELECT TO_SECONDS( NOW() );
-> 63426721458
像TO_DAYS()一样,TO_SECONDS()
不适用于公历(1582)出现之前的值,因为它未考虑更改 calendar 时丢失的日期。对于 1582 年之前的日期(可能在其他地方的较晚年份),此功能的结果不可靠。有关详情,请参见第 12.8 节“ MySQL 使用什么 calendar?”。
像TO_DAYS(),TO_SECONDS()
一样,使用第 11.2 节“日期和时间数据类型”中的规则将日期中的两位数字年份值转换为四位数形式。
在 MySQL 中,零日期定义为'0000-00-00'
,即使该日期本身被视为无效。这意味着,对于'0000-00-00'
和'0000-01-01'
,TO_SECONDS()返回此处显示的值:
mysql> SELECT TO_SECONDS('0000-00-00');
+--------------------------+
| TO_SECONDS('0000-00-00') |
+--------------------------+
| NULL |
+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '0000-00-00' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT TO_SECONDS('0000-01-01');
+--------------------------+
| TO_SECONDS('0000-01-01') |
+--------------------------+
| 86400 |
+--------------------------+
1 row in set (0.00 sec)
无论是否启用ALLOW_INVALID_DATES SQL Server 模式,都是如此。
如果没有参数date
*调用UNIX_TIMESTAMP(),它将返回一个 Unix 时间戳,table 示自'1970-01-01 00:00:00'
UTC 以来的秒数。
如果使用* date
参数调用UNIX_TIMESTAMP(),它将返回自'1970-01-01 00:00:00'
UTC 以来的秒数的参数值。服务器将 date
解释为会话时区中的值,并将其转换为 UTC 中的内部 Unix 时间戳值。 (Client 端可以按照第 5.1.13 节“ MySQL 服务器时区支持”中的说明设置会话时区。) date
参数可以是DATE,DATETIME或TIMESTAMP字符串,也可以是 YYMMDD
, YYMMDDhhmmss
, YYYYMMDD
或 YYYYMMDDhhmmss
*中的数字格式。如果参数包含时间部分,则可以选择包含小数秒部分。
如果没有给出参数或参数不包括小数秒部分,则返回值为整数;如果给出的参数包括小数秒部分,则返回值为DECIMAL。
当* date
*参数是TIMESTAMP列时,UNIX_TIMESTAMP()直接返回内部时间戳记值,而没有隐式的“字符串到 Unix 时间戳记”转换。
参数值的有效范围与TIMESTAMP数据类型的范围相同:'1970-01-01 00:00:01.000000'
UTC 到'2038-01-19 03:14:07.999999'
UTC。如果您将超期日期传递给UNIX_TIMESTAMP(),它将返回0
。
mysql> SELECT UNIX_TIMESTAMP();
-> 1447431666
mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');
-> 1447431619
mysql> SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012');
-> 1447431619.012
如果您使用UNIX_TIMESTAMP()和FROM_UNIXTIME()在非 UTC 时区的值和 Unix 时间戳值之间进行转换,则转换是有损耗的,因为在两个方向上 Map 都不是一对一的。例如,由于诸如夏令时(DST)之类的本地时区更改约定,UNIX_TIMESTAMP()可以将非 UTC 时区中不同的两个值 Map 到相同的 Unix 时间戳值。 FROM_UNIXTIME()会将该值 Map 回仅原始值之一。这是一个示例,使用在MET
时区中不同的值:
mysql> SET time_zone = 'MET';
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
+---------------------------------------+
| 1111885200 |
+---------------------------------------+
mysql> SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
+---------------------------------------+
| 1111885200 |
+---------------------------------------+
mysql> SELECT FROM_UNIXTIME(1111885200);
+---------------------------+
| FROM_UNIXTIME(1111885200) |
+---------------------------+
| 2005-03-27 03:00:00 |
+---------------------------+
Note
要使用诸如'MET'
或'Europe/Amsterdam'
之类的命名时区,必须正确设置时区 table。有关说明,请参见第 5.1.13 节“ MySQL 服务器时区支持”。
如果要减去UNIX_TIMESTAMP()列,则可能需要将它们转换为有符号整数。参见第 12.10 节“Broadcast 函数和运算符”。
以'YYYY-MM-DD'
或* YYYYMMDD
*格式返回当前 UTC 日期作为值,具体取决于该函数是在字符串还是在数字上下文中使用。
mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
-> '2003-08-14', 20030814
以* 'hh:mm:ss'
或 hhmmss
*格式返回当前 UTC 时间作为值,具体取决于该函数是在字符串上下文中还是在数字上下文中使用。
如果使用* fsp
*参数指定从 0 到 6 的小数秒精度,则返回值包括该位数的小数秒部分。
mysql> SELECT UTC_TIME(), UTC_TIME() + 0;
-> '18:07:53', 180753.000000
UTC_TIMESTAMP, UTC_TIMESTAMP([fsp])
以'YYYY-MM-DD hh:mm:ss'
或* YYYYMMDDhhmmss
*格式返回当前 UTC 日期和时间作为值,具体取决于该函数是在字符串上下文中还是在数字上下文中使用。
如果使用* fsp
*参数指定从 0 到 6 的小数秒精度,则返回值包括该位数的小数秒部分。
mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
-> '2003-08-14 18:08:04', 20030814180804.000000
此函数返回* date
的星期数。 WEEK()的两个参数形式使您可以指定星期是从星期日还是星期一开始,以及返回值应在0
到53
还是1
到53
的范围内。如果省略 mode
*参数,则使用default_week_format系统变量的值。参见第 5.1.7 节“服务器系统变量”。
下 table 描述了* mode
*参数的工作方式。
Mode | 一周的第一天 | Range | 第一周是第一周… |
---|---|---|---|
0 | Sunday | 0-53 | 今年的一个星期天 |
1 | Monday | 0-53 | 今年有四天或以上 |
2 | Sunday | 1-53 | 今年的一个星期天 |
3 | Monday | 1-53 | 今年有四天或以上 |
4 | Sunday | 0-53 | 今年有四天或以上 |
5 | Monday | 0-53 | 在今年的一个星期一 |
6 | Sunday | 1-53 | 今年有四天或以上 |
7 | Monday | 1-53 | 在今年的一个星期一 |
对于* mode
*值 table 示“今年有 4 天或更多天”的值,周根据 ISO 8601:1988 进行编号:
-
如果包含 1 月 1 日的一周在新年中有 4 天或更多天,则为第 1 周。
- 否则,它是上一年的最后一周,下周是第 1 周。
mysql> SELECT WEEK('2008-02-20');
-> 7
mysql> SELECT WEEK('2008-02-20',0);
-> 7
mysql> SELECT WEEK('2008-02-20',1);
-> 8
mysql> SELECT WEEK('2008-12-31',1);
-> 53
如果日期在上一年的最后一周,如果您不使用2
,3
,6
或7
作为可选* mode
*参数,则 MySQL 返回0
:
mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
-> 2000, 0
有人可能会争辩说WEEK()应该返回52
,因为给定的日期实际上发生在 1999 年的第 52 周。WEEK()而是返回0
,因此返回值为“给定年份中的星期数”。当与从日期中提取日期部分的其他函数结合使用时,WEEK()函数将可靠地使用。
如果您希望针对包含给定日期的一周的第一天的年份进行评估,请使用0
,2
,5
或7
作为可选的* mode
*参数。
mysql> SELECT WEEK('2000-01-01',2);
-> 52
或者,使用YEARWEEK()函数:
mysql> SELECT YEARWEEK('2000-01-01');
-> 199952
mysql> SELECT MID(YEARWEEK('2000-01-01'),5,2);
-> '52'
返回* date
*(0
=星期一,1
=星期二,…6
=星期日)的工作日索引。
mysql> SELECT WEEKDAY('2008-02-03 22:23:00');
-> 6
mysql> SELECT WEEKDAY('2007-11-06');
-> 1
以1
到53
范围内的数字返回日期的 calendar 周。 WEEKOFYEAR()是与WEEK(date,3)等效的兼容性功能。
mysql> SELECT WEEKOFYEAR('2008-02-20');
-> 8
返回* date
*的年份,范围在1000
到9999
或0
作为“零”日期。
mysql> SELECT YEAR('1987-01-01');
-> 1987
YEARWEEK(date), YEARWEEK(date,mode)
返回日期的年和周。结果中的年份可能与该年份的第一周和最后一周的 date 参数中的年份不同。
mode
参数的工作方式与WEEK()的mode
参数完全相同。对于单参数语法,使用mode
*值 0.与WEEK()不同,default_week_format的值不影响YEARWEEK()。
mysql> SELECT YEARWEEK('1987-01-01');
-> 198652