本节介绍可用于操作时间值的函数。有关每个日期和时间类型具有的值范围以及可以指定值的有效格式的说明,请参见 第 11.2 节“日期和时间数据类型”。
这是一个使用日期函数的示例。以下查询会选择 date_col
过去 30 天内数据:
mysql> SELECT something FROM tbl_name
-> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
需要 date 的函数通常接受 datetime 并忽略时间部分。需要 time 的函数通常接受 datetime 并忽略日期部分。
返回当前日期或时间的函数,只会在查询开始时执行一次。这意味着在单个查询中对函数的多次引用(例如 NOW()
)会产生相同的结果。对于我们而言,单个查询还包括对已存储的程序(存储例程、触发器或事件)和该程序调用的所有子程序的调用。这个原则也适用于CURDATE()
, CURTIME()
, UTC_DATE()
, UTC_TIME()
, UTC_TIMESTAMP()
,以及他们的任何同义词。
CURRENT_TIMESTAMP()
, CURRENT_TIME()
, CURRENT_DATE()
, 和 FROM_UNIXTIME()
的返回值是基于当前会话的时区的。可使用 time_zone
系统变量的会话值。此外, UNIX_TIMESTAMP()
假定其参数是会话时区中的日期时间值。请参阅 第 5.1.13 节,“MySQL 服务器时区支持”。
某些日期函数可用于“零”日期或不完整日期,例如'2001-11-00'
,而其他函数则不能。提取部分日期的函数通常适用于不完整的日期,因此可能会在您可能期望非零值时返回 0。例如:
mysql> SELECT DAYOFMONTH('2001-11-00'), MONTH('2005-00-00');
-> 0, 0
其他函数期望完整的日期,对于不完整的日期返回 NULL。这些函数包括执行日期计算或将日期映射到名称的函数。例如:
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(date,INTERVAL expr unit)
, ADDDATE(expr,days)
当使用第二个参数为 INTERVAL
的形式调用时,ADDDATE()
是 DATE_ADD()
的同义词。相关函数 SUBDATE()
是 DATE_SUB()
的同义词。有关INTERVAL单位参数的信息,请参见时间间隔。有关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'
SUBDATE(date,INTERVAL expr unit)
, SUBDATE(expr,days)
当使用第二个参数为 INTERVAL
的形式调用时,SUBDATE()
是 DATE_SUB()
的同义词。有关INTERVAL单位参数的信息,请参见时间间隔。有关INTERVAL
*unit
*参数的信息,请参阅 Temporal Intervals。
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 形式调用时,MySQL 将其视为要从 expr 中减去的天数。
mysql> SELECT SUBDATE('2008-01-02 12:00:00', 31);
-> '2007-12-02 12:00:00'
DATE_ADD(date,INTERVAL expr unit)
、DATE_SUB(date,INTERVAL expr unit)
进行时间的算术计算。date
是被计算的值;expr
是要加或减去的间隔,是一个字符串,可以表示组合的时间,比如 ‘1:1’,可以以负号开头,表示负的间隔,unit
是 expr
的单位。
有关时间间隔语法的更多信息,包括 unit
说明符的完整列表、expr
每个 unit
值的参数的预期形式以及时间算术中的操作数解释规则,请参阅 时间间隔。
返回值取决于参数:
DATE
:如果date
参数是一个DATE
值,且计算只涉及YEAR
,MONTH
和DAY
部分(即,没有时间部分)。DATETIME
:如果第一个参数是一个DATETIME
或TIMESTAMP
,或者第一个参数是一个DATE
且unit
用到了HOURS
,MINUTES
或SECONDS
。- 否则字符串。
为确保结果为 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'
DATEDIFF(expr1,expr2)
返回 expr1
- expr2
的的天数。expr1
和 expr2
是 date 或 datetime 表达式。只有日期部分参与计算。
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
ADDTIME(expr1,expr2)
ADDTIME()
将 expr2
加到 expr1
并返回结果。 expr1
是一个 time 或 datetime 表达式,expr2
是一个 time 表达式。
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'
SUBTIME(expr1,expr2)
以expr1
的格式返回 expr1
- expr2
。 expr1
是一个 time 或 datetime 表达式,expr2
是一个 time 表达式。
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'
TIMEDIFF(expr1,expr2)
返回 expr1
- expr2
的 time 值。expr1
和 expr2
是 time 或 datetime 表达式。但两者的类型必须相同。
返回的结果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'
PERIOD_ADD(P,N)
在周期 P
的基础上增加 N
个月(格式为 YYMM
或 YYYYMM
)。返回 YYYYMM
格式的值。
Note
周期参数
P
不是日期值。
mysql> SELECT PERIOD_ADD(200801,2);
-> 200803
PERIOD_DIFF(P1,P2)
返回 P1
、P2
之间的月数(P1
- P2
)。P1
、P2
的格式为 YYMM
或 YYYYMM
,不是日期值。
mysql> SELECT PERIOD_DIFF(200802,200703);
-> 11
TIMESTAMP(expr)
, TIMESTAMP(expr1,expr2)
expr
、expr1
的类型为 date 或 datetime,expr2
类型为 time。
-
单个参数:将表达式
expr
转为 datetime 并返回。 -
两个参数:将 time 表达式
expr2
加到expr1
,并返回 datetime。
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)
将整数表达式 interval
累加到 date 或 datetime 表达式 datetime_expr
上。unit
参数应为以下值之一:MICROSECOND
、SECOND
、 MINUTE
、HOUR
、 DAY
、WEEK
、 MONTH
、QUARTER
、 或 YEAR
。
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
,参数是 date 或 datetime 表达式。unit
指定返回值的单位,应为以下值之一:MICROSECOND
、SECOND
、 MINUTE
、HOUR
、 DAY
、WEEK
、 MONTH
、QUARTER
、 或 YEAR
。
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
这个函数是
expr2 - expr1
,其他的 diff 函数都是expr1 - expr2
。
当前的日期、时间
NOW([fsp])
以'YYYY-MM-DD hh:mm:ss
或 YYYYMMDDhhmmss
格式返回当前日期,这取决于函数是在字符串还是数字上下文中使用。该值以会话时区表示。
如果给出 fsp
参数指定了 0 到 6 的小数秒精度,则返回值包括多位的小数秒。
mysql> SELECT NOW();
-> '2007-12-15 23:50:26'
mysql> SELECT NOW() + 0;
-> 20071215235026.000000
NOW()
返回一个常量时间,表示语句开始执行的时间。(在存储的函数或触发器中, NOW()
返回函数或触发语句开始执行的时间。)这与 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()
时返回该值。将时间戳设置为0将取消此效果,以便 NOW()
再次返回当前日期和时间。
SYSDATE([fsp])
以'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()
的不确定性也意味着索引不能用于评估引用它的表达式。
CURDATE()
以 'YYYY-MM-DD'
或 YYYYMMDD
格式返回当前日期,这取决于函数是在字符串还是数字上下文中使用。
mysql> SELECT CURDATE();
-> '2008-06-13'
mysql> SELECT CURDATE() + 0;
-> 20080613
CURRENT_DATE
, CURRENT_DATE()
CURDATE()
的同义词
CURTIME([fsp])
以 'hh:mm:ss'
或 hhmmss
格式返回当前日期,这取决于函数是在字符串还是数字上下文中使用。
如果给出 fsp
参数指定了 0 到 6 的小数秒精度,则返回值包括多位的小数秒。
mysql> SELECT CURTIME();
-> '19:50:15'
mysql> SELECT CURTIME() + 0;
-> 195015
mysql> SELECT CURTIME(5);
-> '19:50:16.00062'
mysql> SELECT CURTIME(5) + 0;
-> 195016.01581
CURRENT_TIME
, CURRENT_TIME([fsp])
CURTIME()
的同义词
LOCALTIME
, LOCALTIME([fsp])
,LOCALTIMESTAMP
, LOCALTIMESTAMP([fsp])
,CURRENT_TIMESTAMP
, CURRENT_TIMESTAMP([fsp])
NOW()
的同义词。
UTC_DATE
以 'YYYY-MM-DD'
或 YYYYMMDD
格式返回当前 UTC date,这取决于函数是在字符串还是数字上下文中使用。
mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
-> '2003-08-14', 20030814
UTC_TIME
, UTC_TIME([fsp])
以 'hh:mm:ss'
或 hhmmss
格式返回当前 UTC time,这取决于函数是在字符串还是数字上下文中使用。
如果给出 fsp
参数指定了 0 到 6 的小数秒精度,则返回值包括多位的小数秒。
mysql> SELECT UTC_TIME(), UTC_TIME() + 0, UTC_TIME(6) + 0;
-> '09:29:55', 92955, 92955.068393
UTC_TIMESTAMP
, UTC_TIMESTAMP([fsp])
以'YYYY-MM-DD hh:mm:ss
或 YYYYMMDDhhmmss
格式返回当前 UTC datetime,这取决于函数是在字符串还是数字上下文中使用。
如果给出 fsp
参数指定了 0 到 6 的小数秒精度,则返回值包括多位的小数秒。
mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
-> '2003-08-14 18:08:04', 20030814180804.000000
第几天、周几、第几月、最后一天
DAYOFMONTH(date)
返回一个月中的第几天,范围是 1 - 31。如果参数中含有 zero day。会返回 0,比如 ‘0000-00-00’、‘2008-00-00’。
mysql> SELECT DAYOFMONTH('2007-02-03');
-> 3
DAYOFWEEK(date)
返回日期(1 =星期日,2 =星期一,…,7 =星期六)的星期索引。这些索引值对应于ODBC标准。
mysql> SELECT DAYOFWEEK('2007-02-03');
-> 7
DAYOFYEAR(date)
返回一年中的第几天,范围 1 - 366。
mysql> SELECT DAYOFYEAR('2007-02-03');
-> 34
LAST_DAY(date)
接受 date 或 datetime 并返回一个月最后一天的对应值。如果参数无效,返回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
提取部分字段
EXTRACT(unit FROM date)
EXTRACT()
使用与 DATE_ADD()
相同类型的 unit
说明符,但是它的作用是从 date
中提取一部分,而不是执行日期计算。有关单位参数的信息,请参阅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
DATE(expr)
提取 date 或 datetime 表达式的 date 部分。
mysql> SELECT DATE('2003-12-31 01:02:03');
-> '2003-12-31'
TIME(expr)
提取 date 或 datetime 表达式的 time 部分。
此函数对于基于语句的复制是不安全的。如果您在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'
YEAR(date)
只返回年部分。范围 1000 - 9999,或者 0 对于零日期
mysql> SELECT YEAR('1987-01-01');
-> 1987
QUARTER(date)
返回季度, 范围 1
为4
。
mysql> SELECT QUARTER('2008-04-01');
-> 2
MONTH(date)
只返回月份,范围为 1-12,代表1到12月,或者0,例如’0000-00-00’或’ 2008-- 00-00’,其中月的部分为0。
mysql> SELECT MONTH('2008-02-03');
-> 2
MONTHNAME(date)
返回日期所在月份的全名。用于该名称的语言由lc_time_names
系统变量的值控制 (第 10.16 节,“MySQL 服务器区域设置支持”)。
mysql> SELECT MONTHNAME('2008-02-03');
-> 'February'
DAY(date)
DAYOFMONTH()
的同义词。只返回天的部分
DAYNAME(date)
返回星期几的名称。用于名称的语言由 lc_time_names
系统变量的值控制(第 10.16 节,“MySQL 服务器区域设置支持”)。
mysql> SELECT DAYNAME('2007-02-03');
-> 'Saturday'
HOUR(time)
只返回小时部分。范围是 0 - 23。但是,TIME 值的范围实际上要大得多,所以HOUR可以返回大于23的值。
mysql> SELECT HOUR('10:05:03');
-> 10
mysql> SELECT HOUR('272:59:59');
-> 272
MINUTE(time)
只返回分钟部分。
mysql> SELECT MINUTE('2008-02-03 10:05:03');
-> 5
SECOND(time)
只返回秒部分。范围 0 - 59.
mysql> SELECT SECOND('10:05:03');
-> 3
MICROSECOND(expr)
只返回微秒部分。
mysql> SELECT MICROSECOND('12:00:00.123456');
-> 123456
mysql> SELECT MICROSECOND('2019-12-31 23:59:59.000010');
-> 10
WEEK(date[,mode])
返回 date 属于本年的第几周。两个参数的形式能够指定一周是从星期日还是星期一开始,以及返回值是0 - 53
还是 1 - 53
。
如果 mode
省略参数,则使用系统变量的值 default_week_format
。请参阅 第 5.1.7 节,“服务器系统变量”。
下表描述了 mode
参数的工作原理。
Mode | First day of week | Range | Week 1 is the first week … |
---|---|---|---|
0 | Sunday | 0-53 | with a Sunday in this year |
1 | Monday | 0-53 | with 4 or more days this year |
2 | Sunday | 1-53 | with a Sunday in this year |
3 | Monday | 1-53 | with 4 or more days this year |
4 | Sunday | 0-53 | with 4 or more days this year |
5 | Monday | 0-53 | with a Monday in this year |
6 | Sunday | 1-53 | with 4 or more days this year |
7 | Monday | 1-53 | with a Monday in this year |
对于 mode
具有的意义价值 “有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
如果 date 在上一年的最后一周,如果不使用 2
, 3
, 6
, 或7
作为可选 mode
参数,MySQL 将返回 0:
mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0);
-> 2000, 0
有人可能会争辩说,应该返回 52,因为给定的日期实际上发生在 1999 年的第 52 周。而是WEEK()
返回 0,因此返回值是给定年份中的周数。
如果您喜欢计算包含给定日期每周第一天的年份的结果,则使用0、2、5或7作为可选的模式参数。
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'
WEEKDAY(date)
返回日期(0
= 星期一,1
= 星期二,… 6
= 星期日)的星期索引。
mysql> SELECT WEEKDAY('2008-02-03 22:23:00');
-> 6
mysql> SELECT WEEKDAY('2007-11-06');
-> 1
WEEKOFYEAR(date)
WEEKOFYEAR()
是一个兼容函数,它等价于 。 WEEK(date,3)
mysql> SELECT WEEKOFYEAR('2008-02-20');
-> 8
YEARWEEK(date)
, YEARWEEK(date,mode)
返回 date 的年和周。对于每年的第一周或最后一周,结果中的年份可能与参数中的不一致。
mode
参数的工作方式与 WEEK()
完全相同。对于一个参数的,mode
值为 0。不像 WEEK()
, default_week_format
的值不会影响YEARWEEK()
。
mysql> SELECT YEARWEEK('1987-01-01');
-> 198652
与 WEEK()
不同的是,周数是与返回值中的年相对应的。
转换与格式化
CONVERT_TZ(dt,from_tz,to_tz)
CONVERT_TZ()
将dt
从 from_tz
转换到给定的时区 to_tz
。时区的指定如 第 5.1.13 节,“MySQL 服务器时区支持”中所述。如果参数无效,则返回 NULL
。
当从 from_tz
转换到 UTC 时,如果该值超出了 TIMESTAMP
类型的支持范围,则不会发生转换。该TIMESTAMP
范围在第 11.2.1 节,“日期和时间数据类型语法”中描述。
# 我在测试时,第一个返回了 NULL ???
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'
,必须正确设置时区表。有关说明,请参阅 第 5.1.13 节,“MySQL 服务器时区支持”。
DATE_FORMAT(date,format)
将时间格式化成字符串。下表中显示的说明符可以在格式字符串中使用。格式说明符字符前必须有%字符。说明符也适用于其他函数:STR_TO_DATE()
, TIME_FORMAT()
, UNIX_TIMESTAMP()
。
说明符 | 描述 |
---|---|
%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() 模式一 |
%V | 周 ( 01 … 53 ),其中星期日是一周的第一天; WEEK() 模式2;与 %X |
%v | 周 ( 01 … 53 ),其中星期一是一周的第一天; WEEK() 模式3;与 %x |
%W | 工作日名称 ( Sunday … Saturday ) |
%w | 星期几(0 =星期日… 6 =星期六) |
%X | 周的年份,其中星期日是一周的第一天,数字,四位数字;与%V |
%x | 一周的年份,其中星期一是一周的第一天,数字,四位数字;与%v |
%Y | 年份,数字,四位数字 |
%y | 年份,数字(两位数) |
%% | 文字% 字符 |
%* x* | x , 对于上面未列出的任何 “ x ” |
由于MySQL允许存储不完整的日期,例如“2014-00-00”,所以月和日的指定范围以0开始。
用于日期和月份名称和缩写的语言由lc_time_names
系统变量的值控制 (第 10.16 节,“MySQL 服务器区域设置支持”)。
对于%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'
TIME_FORMAT(time,format)
与 DATE_FORMAT()
函数的用法一样 ,但 format
字符串只包含小时、分钟、秒和微秒的格式说明符。其他说明符产生一个NULL
值 or 0
。
如果该 time
值中的 hour 大于23
,则 %H
和%k
产生的值比通常范围 0..23
大。其他 hour 格式说明符生成以 12 为模的小时值。
mysql> SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l');
-> '100 100 04 04 4'
FROM_DAYS(N)
给定一个天数 N
,返回一个 DATE
值。(自 0 年以来的天数)。
mysql> SELECT FROM_DAYS(730669);
-> '2000-07-03'
使用FROM_DAYS()
时注意旧的日期。它不适用于公历 (1582) 出现之前的值。请参阅 第 12.9 节,“MySQL 使用什么日历?” .
TO_DAYS(date)
将 date 转换成天数(自 0 年以来的天数)。
mysql> SELECT TO_DAYS(950501);
-> 728779
mysql> SELECT TO_DAYS('2007-10-07');
-> 733321
TO_DAYS()
不适用于格里高利历 (1582) 出现之前的值,因为它没有考虑更改日历时丢失的天数。对于 1582 年之前的日期(在其他语言环境中可能是更晚的一年),此函数的结果不可靠。请参阅 第 12.9 节,“MySQL 使用什么日历?” , 详情。
请记住,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 服务器模式,都是如此。
FROM_UNIXTIME(unix_timestamp[,format])
返回表示 unix_timestamp
的 datetime 或字符串。返回值使用会话的时区。(客户端可以设置会话时区,如第 5.1.13 节,“MySQL 服务器时区支持”中所述。) unix_timestamp
是一个内部时间戳值,表示自'1970-01-01 00:00:00'
UTC 以来的秒数 ,例如由UNIX_TIMESTAMP()
函数生成。
如果 format
省略,则返回 DATETIME
。
如果 unix_timestamp
是 integer,则 DATETIME
的小数秒精度为零。当 unix_timestamp
是一个 decimal, DATETIME
的小数秒精度与十进制值的精度相同,最多为6。 unix_timestamp
是浮点数,DATETIME
的分数秒精度为 6。
format
使用与 DATE_FORMAT()
函数的格式字符串相同的方式格式化结果 。如果指定了 format
,则返回的值为 VARCHAR
。
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 时间戳值之间进行转换,则转换是有损的,因为映射不是双向的一对一。详情请参见UNIX_TIMESTAMP()
函数说明 。
UNIX_TIMESTAMP([date])
如果不带 date
参数,则返回当前的 Unix 时间戳,表示自'1970-01-01 00:00:00'
以来的 UTC 秒数。
如果带 date
参数,则返回参数的 Unix 时间戳。服务器使用会话的时区将其转换成 UTC Unix 时间戳。date
参数可以是一个 DATE
, DATETIME
或 TIMESTAMP
字符串,或 YYMMDD
, YYMMDDhhmmss
, YYYYMMDD
,或者 *YYYYMMDDhhmmss
*格式的数字。如果参数包含 time 部分,则可以有选择的包含小数秒部分。
如果没有给出参数或参数不包含小数秒部分,则返回整数。
当参数为 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 时间戳值之间进行转换,则转换是有损的,因为映射不是双向的一对一。例如,由于本地时区更改的约定,例如夏令时 (DST),可以将非 UTC 时区中不同的两个值映射到相同的 Unix 时间戳值。 FROM_UNIXTIME()
将该值仅映射回原始值之一。这是一个示例,使用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 |
+---------------------------+
GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})
TODO
MAKEDATE(year,dayofyear)
返回给定 year 和 dayofyear(本年的第几天) 的日期。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
MAKETIME(hour,minute,second)
根据 hour
, *minute
和 second
返回一个 time。该second
*参数可以有小数部分。
mysql> SELECT MAKETIME(12,15,30);
-> '12:15:30'
SEC_TO_TIME(seconds)
将秒转换成 time 并返回。结果的范围仅限于 TIME
数据类型的范围。如果参数对应于该范围之外的值,则会出现警告。
mysql> SELECT SEC_TO_TIME(2378);
-> '00:39:38'
mysql> SELECT SEC_TO_TIME(2378) + 0;
-> 3938
TIME_TO_SEC(time)
将 time 转换成秒。
mysql> SELECT TIME_TO_SEC('22:23:00');
-> 80580
mysql> SELECT TIME_TO_SEC('00:39:38');
-> 2378
STR_TO_DATE(str,format)
将字符串解析成时间,这是DATE_FORMAT()
函数的逆函数。它需要一个字符串*str
和一个格式字符串format
。 如果格式字符串包含日期和时间部分,则STR_TO_DATE()
返回一个 DATETIME
值,如果字符串只包含日期或时间部分,则返回一个 DATE
或 TIME
值。如果从中提取的日期、时间或日期时间值不str
* 合法,则STR_TO_DATE()
返回NULL
并产生警告。
服务器扫描 str
,试图将 format
匹配到它。格式字符串可以包含以 %
开头的文字字符和格式说明符。str
中的字面字符必须与 str
中的字面字符匹配。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'
TO_SECONDS(expr)
将 expr 转换成秒数(自 0 年以来的秒数)。
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) 出现之前的值。因为它没有考虑更改日历时丢失的天数。对于 1582 年之前的日期(在其他语言环境中可能是更晚的一年),此函数的结果不可靠。
像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 服务器模式,都是如此。