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

数据库杂货铺 2021-06-18

985

● TIMESTAMPADD(unit,interval,datetime_expr)

将整数表达式 interval 添加到日期或日期时间表达式 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 是日期或日期时间表达式。可以其中一个表达式是日期，另一个表达式是日期时间；如果需要，日期值将被视为时间部分为 '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

注意

此函数的日期或日期时间参数的顺序与使用2个参数调用 TIMESTAMP() 函数时使用的顺序相反。

● TIME_FORMAT(time,format)

此函数用法与 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_TO_SEC(time)

返回将 time 参数转换成秒后的值

mysql> SELECT TIME_TO_SEC('22:23:00');
        -> 80580
mysql> SELECT TIME_TO_SEC('00:39:38');
        -> 2378

● TO_DAYS(date)

给定一个日期 date，返回天数（从第 0 年开始的天数）。

mysql> SELECT TO_DAYS(950501);
        -> 728779
mysql> SELECT TO_DAYS('2007-10-07');
        -> 733321

TO_DAYS() 不适用于格里高利历（1582）出现之前的值，因为它不考虑更改日历时丢失的天数。对于 1582 年之前的日期（在其他地区可能是晚一年），此函数的结果不可靠。

请记住，MySQL 将日期中的两位数年份值转换为四位数形式。例如，'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 服务器模式，都是如此。

● TO_SECONDS(expr)

给定日期或日期时间 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）出现之前的值，因为它不考虑更改日历时丢失的天数。对于 1582 年之前的日期（在其他地区可能是晚一年），此函数的结果不可靠。

如同 TO_DAYS()，TO_SECONDS() 将日期中的两位数年份值转换为四位数形式。

在 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 服务器模式，都是如此。

● UNIX_TIMESTAMP([date])

如果在调用 UNIX_TIMESTAMP() 时不带 date 参数，它将返回一个 UNIX 时间戳，表示自 '1970-01-01 00:00:00' UTC 以来的秒数。

如果使用 date 参数调用 UNIX_TIMESTAMP()，它将以秒的形式返回自 '1970-01-01 00:00:00' UTC 以来的参数值。服务器将日期解释为会话时区中的值，并将其转换为 UTC 格式的内部 Unix 时间戳值。date 参数可以是 DATE、DATETIME 或 TIMESTAMP 字符串，也可以是 YYMMDD、YYMMDDhhmmss、YYYYMMDD 或 YYYYMMDDDHHMMSS 格式的数字。如果参数包含时间部分，则可以包含小数秒部分。

如果未给定参数或参数不包含小数秒部分，则返回值为整数；如果给定的参数包含小数秒部分，则返回值为 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 时间戳值之间进行转换，则转换是有损的，因为在两个方向上的映射不是一对一的。例如，由于本地时区更改（如夏令时（DST））的约定，UNIX_TIMESTAMP() 可以将非 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       |
+---------------------------+

● UTC_DATE, UTC_DATE()

根据函数是在字符串还是数字上下文中使用，以 'YYYY-MM-DD' 或 YYYYMMDD 格式返回当前 UTC 日期。

mysql> SELECT UTC_DATE(), UTC_DATE() + 0;
        -> '2003-08-14', 20030814

● UTC_TIME, UTC_TIME([fsp])

依据于函数在字符串或数字上下文中使用，将当前 UTC 时间以 'hh:mm:ss' 或 hhmmss 格式的值返回。

如果给定 fsp 参数以指定 0 到 6 之间的小数秒精度，则返回值将包含相应的小数秒部分。

mysql> SELECT UTC_TIME(), UTC_TIME() + 0;
        -> '18:07:53', 180753.000000

● UTC_TIMESTAMP, UTC_TIMESTAMP([fsp])

依据函数是在字符串还是数字上下文中使用，将当前 UTC 日期和时间以 'YYYY-MM-DD hh:mm:ss' 或 YYYYMMDDhhmmss 格式返回。

如果给定 fsp 参数指定 0 到 6 之间的小数秒精度，则返回值将包含相应的小数秒部分。

mysql> SELECT UTC_TIMESTAMP(), UTC_TIMESTAMP() + 0;
        -> '2003-08-14 18:08:04', 20030814180804.000000

● WEEK(date[,mode])

此函数返回 date 的周数。WEEK() 的双参数形式可以指定星期是从星期日开始还是从星期一开始，以及返回值应在 0 到 53 之间还是 1 到 53 之间。如果省略 mode 参数，则使用 default_week_format 系统变量的值。

下表描述了 mode 参数的工作方式。

模式	周第一天	范围	1 是第一周
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

对于具有 “with 4 or more days this year” 含义的 mode 值，根据 ISO 8601:1988 对周进行编号：

■ 如果包含1月1日的一周在新的一年中有4天或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'

● WEEKDAY(date)

返回 date 的星期几索引（0 = 周一，1 = 周二，…6 = 周日）。

mysql> SELECT WEEKDAY('2008-02-03 22:23:00');
        -> 6
mysql> SELECT WEEKDAY('2007-11-06');
        -> 1

● WEEKOFYEAR(date)

返回 date 的日历周，范围在 1 到 53 之间的数字。WEEKOFYEAR() 相当于 WEEK(date,3)。

mysql> SELECT WEEKOFYEAR('2008-02-20');
        -> 8

● YEAR(date)

返回 date 的年份，范围为 1000 到 9999，或 0 表示“零”日期。

mysql> SELECT YEAR('1987-01-01');
        -> 1987

● YEARWEEK(date), YEARWEEK(date,mode)

返回日期的年和周。结果中的年份可能与日期参数所在年中第一周和最后一周的年份不同。

mode 参数的工作方式与 WEEK() 的 mode 参数完全相同。对于单个参数的使用语法，mode 值使用 0。与 WEEK() 不同，default_week_format 的值不影响 YEARWEEK()。

mysql> SELECT YEARWEEK('1987-01-01');
        -> 198652

星期数不同于 WEEK() 函数为可选参数值 0 或 1 返回的值（0），因为 WEEK() 随后返回给定年份上下文中的星期。

官方文档：

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

mysql

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

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

评论