MySQL的函数和运算符 - 数值函数和运算符 - 日期和时间函数（3）

数据库杂货铺 2021-06-14

479

● LOCALTIME, LOCALTIME([fsp])

LOCALTIME 和 LOCALTIME() 是 NOW() 的同义词。

● LOCALTIMESTAMP, LOCALTIMESTAMP([fsp])

LOCALTIMESTAMP 和 LOCALTIMESTAMP() 是 NOW() 的同义词。

● MAKEDATE(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 参数计算的时间值。

second 参数可以有小数部分。

mysql> SELECT MAKETIME(12,15,30);
        -> '12:15:30'

● MICROSECOND(expr)

返回时间或日期时间表达式 expr 的微秒数，范围是 0 到 999999 之间的数字。

mysql> SELECT MICROSECOND('12:00:00.123456');
        -> 123456
mysql> SELECT MICROSECOND('2019-12-31 23:59:59.000010');
        -> 10

● MINUTE(time)

返回 time 的分钟数，范围为 0 到 59。

mysql> SELECT MINUTE('2008-02-03 10:05:03');
        -> 5

● MONTH(date)

返回 date 的月份，1月到12月，分别返回 1 到 12 之间的数字，对于月份部分为零的日期，如 '0000-00-00' 或 '2008-00-00'，返回 0。

mysql> SELECT MONTH('2008-02-03');
        -> 2

● MONTHNAME(date)

返回 date 所在月份全名。名称的语言由 lc_time_names 系统变量的值控制。

mysql> SELECT MONTHNAME('2008-02-03');
        -> 'February'

● 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() 都返回该值。将时间戳设置为零将取消此效果，以便 NOW() 再次返回当前日期和时间。

● PERIOD_ADD(P,N)

将 N 个月添加到时间段 P（格式为 YYMM 或 YYYYMM）。返回格式为 YYYYMM 的值。

注意

时间段参数 P 不是日期值。

mysql> SELECT PERIOD_ADD(200801,2);
        -> 200803

● PERIOD_DIFF(P1,P2)

返回时间段 P1 和 P2 之间相差的月数。P1 和 P2 的格式应为 YYMM 或 YYYYMM。注意，周期参数 P1 和 P2 不是日期值。

mysql> SELECT PERIOD_DIFF(200802,200703);
        -> 11

● QUARTER(date)

返回 date 的季度，范围为 1 到 4。

mysql> SELECT QUARTER('2008-04-01');
        -> 2

● SECOND(time)

返回 time 的秒数，范围为 0 到 59。

mysql> SELECT SECOND('10:05:03');
        -> 3

● SEC_TO_TIME(seconds)

将 seconds 参数，转换为小时、分钟和秒，返回 TIME 值。结果的范围限制为 TIME 数据类型的范围。如果参数对应的值超出该范围，则会出现警告。

mysql> SELECT SEC_TO_TIME(2378);
        -> '00:39:38'
mysql> SELECT SEC_TO_TIME(2378) + 0;
        -> 3938

● STR_TO_DATE(str,format)

这是 DATE_FORMAT() 函数的逆函数。它接收一个字符串 str 和一个格式化字符串 format。如果格式字符串同时包含日期和时间部分，STR_TO_DATE() 将返回一个 DATETIME 值；如果格式字符串仅包含日期或时间部分，则返回一个 DATE 或 TIME 值。如果从 str 中提取的日期、时间或日期时间值是非法的，STR_TO_DATE() 将返回 NULL 并产生警告。

服务器扫描 str，试图将 format 与之匹配。格式字符串可以包含字面量字符和以 % 开头的格式说明符。format 中的字面量字符必须与 str 中的字面量字符匹配。format 中的格式说明符必须与 str 中的日期或时间部分匹配。

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'

允许“零”日期或部分值为 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

注意

不能使用格式 "%X%V" 将年-周字符串转换为日期，因为如果周跨越月边界，则年和周的组合不能唯一标识年和月。要将年-周转换为日期，还应指定星期几：

mysql> SELECT STR_TO_DATE('200442 Monday', '%X%V %W');
        -> '2004-10-18'

● SUBDATE(date,INTERVAL expr unit), 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 使用整数值。在这种情况下，它被解释为从日期或日期时间表达式 expr 中减去的天数。

mysql> SELECT SUBDATE('2008-01-02 12:00:00', 31);
        -> '2007-12-02 12:00:00'

● SUBTIME(expr1,expr2)

SUBTIME() 返回 expr1 − expr2，格式与 expr1 格式相同的值。expr1 是时间或日期时间表达式，expr2 是时间表达式。

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'

● 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() 的不确定性还意味着计算引用它的表达式时不能使用索引。

● TIME(expr)

提取时间或日期时间表达式 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)

TIMEDIFF() 返回 expr1 − expr2 的时间值。expr1 和 expr2 是时间或日期时间表达式，但两者必须是同一类型。

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)

对于单个参数，此函数将日期或日期时间表达式 expr 作为日期时间值返回。使用两个参数，它将时间表达式 expr2 添加到日期或日期时间表达式 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'

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_get-format

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - 数值函数和运算符 - 日期和时间函数（3）

评论