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

数据库杂货铺 2021-06-13

457

日期和时间函数

● DATE(expr)

提取日期或日期时间表达式 expr 的日期部分。

mysql> SELECT DATE('2003-12-31 01:02:03');
        -> '2003-12-31'

● DATEDIFF(expr1,expr2)

DATEDIFF() 返回 expr1 − expr2 表示的从一个日期到另一个日期的天数值。expr1 和 expr2 是日期或日期时间表达式。计算中仅使用值的日期部分。

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(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

这些函数执行日期运算。date 参数指定开始日期或日期时间值。expr 是一个表达式，指定要从开始日期加上或减去的间隔值。expr 作为字符串计算；它可能以 - 开始，表示负间隔。unit 是一个关键字，表示表达式应在其中进行处理的单位。

返回值取决于参数：

■ 如果 date 参数是 DATE 值，并且计算只涉及 YEAR、MONTH 和 DAY 部分（即没有时间部分），则返回 DATE。

■ 如果第一个参数是 DATETIME（或 TIMESTAMP）值，或者第一个参数是 DATE，并且 unit 值使用 HOURS、MINUTES 或 SECONDS，则返回 DATETIME。

■ 否则返回字符串。

为了确保结果是 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'

● DATE_FORMAT(date,format)

根据 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() mode 参数为 0
%u	周(00..53), 其中星期一是一周的第一天；WEEK() mode 参数为 1
%V	周(01..53), 其中星期日是一周的第一天；WEEK() mode 参数为 2；与 %X 一起使用
%v	周(01..53), 其中星期一是一周的第一天；WEEK() mode 参数为 3；与 %x 一起使用
%W	星期名称 (Sunday..Saturday)
%w	一周中的天(0=Sunday..6=Saturday)
%X	星期天是周的第一天的星期的年份，数字，四位数；与 %V 一起使用
%x	星期一是周的第一天的星期的年份，数字，四位数；与 %v 一起使用
%Y	年份，数字，四位数
%y	年份，数字（两位数）
%%	字面量 % 字符
%x	x, 表示所有上面没有列出的 “x”

月份和日期说明符的范围从零开始，因为 MySQL 允许存储不完整的日期，例如 '2014-00-00'。

用于日和月名称及缩写的语言由 lc_time_names 系统变量的值控制。

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(date,INTERVAL expr unit)

参加 DATE_ADD()。

● DAY(date)

DAY() 是 DAYOFMONTH() 的同义词。

● DAYNAME(date)

返回 date 的星期名称。用于名称的语言由 lc_time_names 系统变量的值控制。

mysql> SELECT DAYNAME('2007-02-03');
        -> 'Saturday'

● DAYOFMONTH(date)

返回 date 是一个月中的第几天，范围为 1 到 31，对于形如 '0000-00-00' 或 '2008-00-00' 等包含零天部分的日期返回 0。

mysql> SELECT DAYOFMONTH('2007-02-03');
        -> 3

● DAYOFWEEK(date)

返回 date 的星期索引（1 = Sunday, 2 = Monday, …, 7 = Saturday）。这些索引值对应于 ODBC 标准。

mysql> SELECT DAYOFWEEK('2007-02-03');
        -> 7

● DAYOFYEAR(date)

返回 date 是一年中的第几天，范围从 1 到 366。

mysql> SELECT DAYOFYEAR('2007-02-03');
        -> 34

● EXTRACT(unit FROM date)

EXTRACT() 函数使用与 DATE_ADD() 或 DATE_SUB() 函数相同的 unit 说明符，但是从日期中提取某部分，而不是执行日期计算。

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

● FROM_DAYS(N)

给定一个日数 N，返回一个 DATE 值。

mysql> SELECT FROM_DAYS(730669);
        -> '2000-07-03'

● FROM_UNIXTIME(unix_timestamp[,format])

返回 unix_timestamp 参数以 'YYYY-MM-DD hh:mm:ss' 或 YYYYMMDDhhmmss 格式表示的值，具体取决于函数是在字符串上下文中使用还是在数字上下文中使用。unix_timestamp 是一个内部时间戳值，表示自 '1970-01-01 00:00:00' UTC 以来的秒数，诸如由 UNIX_TIMESTAMP() 函数生成的秒数。

返回值以会话时区表示。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'

注意

如果使用 UNIX_TIMESTAMP() 和 FROM_UNIXTIME() 在非 UTC 时区中的值和 UNIX 时间戳值之间进行转换，则转换是有损的，因为双向的映射不是一对一的。

● GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})

返回格式字符串。此函数与 DATE_FORMAT() 和 STR_TO_DATE() 函数结合使用非常有用。

第一个和第二个参数的可能值会产生几个可能的格式字符串（有关使用的说明符，请参阅 DATE_FORMAT() 函数说明中的表格）。ISO 格式指的是 ISO 9075，而不是 ISO 8601。

函数调用	结果
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'

● HOUR(time)

返回 time 中的小时。对于日期中的时间值，返回值的范围是 0 到 23。但是，TIME 值的范围实际上要大得多，因此 HOUR 可以返回大于 23 的值。

mysql> SELECT HOUR('10:05:03');
        -> 10
mysql> SELECT HOUR('272:59:59');
        -> 272

● LAST_DAY(date)

接收日期或日期时间值并返回当月最后一天的相应值。如果参数无效，则返回 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

官方文档：

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

mysql

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

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

评论