Mysql中date_format()函数及interval关键字的用法

一、date_format的用法

1. 作用

格式化日期时间，将日期按照自己想要的格式输出。

2. 语法

date_format(date, format)：date参数是合法的日期，format参数是规定日期输出的格式。

3. 示例

SELECT CURRENT_DATE AS time1, DATE_FORMAT(CURRENT_DATE, '%Y%m%d') AS time2;

--输出结果
time1		time2
2022-07-01	20220701

4. 常用的符号标识

符号标识	说明	案例	结果
%a	缩写星期名(Sun-Sat)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%a’) AS time;	Mon
%b	缩写月名(Jan-Dec)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%b’) AS time;	Jul
%c	月，数值(1-12)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%c’) AS time;	7
%D	带有英文前缀的月份的天(1st, 2nd, 3rd, 等等)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%D’) AS time;	4th
%d	月份中的天数，数值(00-31)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%d’) AS time;	04
%e	月份中的天数，数字(0-31)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%e’) AS time;	4
%f	微秒	SELECT DATE_FORMAT(‘2022-07-04 15:10:25.123400’, ‘%f’) AS time;	123400
%H	小时 (00-23)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%H’) AS time;	15
%h	小时 (01-12)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%h’) AS time;	03
%I	小时 (01-12)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%I’) AS time;	03
%i	分钟，数值(00-59)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%i’) AS time;	10
%j	一年中的天数 (001-366)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%j’) AS time;	185
%k	小时 (0-23)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%k’) AS time;	15
%l	小时 (1-12)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%l’) AS time;	3
%M	月名(January-December)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%M’) AS time;	July
%m	月，数值(00-12)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%m’) AS time;	07
%p	AM 或 PM	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%p’) AS time;	PM
%r	时间，12-小时(hh:mm:ss AM 或 PM)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%r’) AS time;	03:10:25 PM
%S	秒(00-59)	SELECT DATE_FORMAT(‘2022-07-04 15:10:03’, ‘%S’) AS time;	03
%s	秒(00-59)	SELECT DATE_FORMAT(‘2022-07-04 15:10:03’, ‘%s’) AS time;	03
%T	时间，24-小时 (hh:mm:ss)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%T’) AS time;	15:10:25
%U	周 (00-53) 星期日是一周的第一天	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%U’) AS time;	27
%u	周 (00-53) 星期一是一周的第一天	SELECT DATE_FORMAT(‘2022-07-03 15:10:25’, ‘%u’) AS time;	26
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%V’) AS time;	27
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用	SELECT DATE_FORMAT(‘2022-07-03 15:10:25’, ‘%v’) AS time;	26
%W	星期名(Sunday-Saturday)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%W’) AS time;	Monday
%w	一个星期中的天数 (0=星期日, 6=星期六)	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%w’) AS time;	1
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%X’) AS time;	2022
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%x’) AS time;	2022
%Y	年，4 位	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%Y’) AS time;	2022
%y	年，2 位	SELECT DATE_FORMAT(‘2022-07-04 15:10:25’, ‘%y’) AS time;	22

获取年月日时分秒：

SELECT DATE_FORMAT('2022-07-04 15:10:25', '%Y-%m-%d %H:%i:%s') AS time;
--或者
SELECT DATE_FORMAT('2022-07-04 15:10:25', '%Y-%m-%d %T') AS time;

二、interval的用法

1. 作用

interval 作为一个关键字时，表示为时间间隔，常用在date_add()、date_sub()、subdate()，函数中，常用于时间的加减法。

2. 语法

例子：

--示例1
SELECT CURRENT_TIMESTAMP AS time1, DATE_FORMAT(CURRENT_TIMESTAMP - interval '1 1 1 1' DAY_SECOND, '%Y-%m-%d %H:%i:%s') AS time2;
--结果
time1					time2
2022-07-05 15:17:57		2022-07-04 14:16:56

--示例2
SELECT CURRENT_TIMESTAMP AS time1, DATE_FORMAT(CURRENT_TIMESTAMP - interval '1 1' DAY_SECOND, '%Y-%m-%d %H:%i:%s') AS time2;
--结果
time1					time2
2022-07-05 15:17:57		2022-07-05 15:16:56

interval 后面的数字可以用数字格式或者字符格式，当用时间单位是此类YEAR_MONTH（几年又几月）时，必须用字符格式。

‘1 3’ YEAR_MONTH 的配置就是代表1年3个月，两个数字之间的间隔符用等号，空格，下划线，中划线等等的都可以。

3. 时间单位表

时间单位	备注
MICROSECOND	微秒
SECOND	秒
MINUTE	分钟
HOUR	小时
DAY	天
WEEK	周
MONTH	月
QUARTER	季
YEAR	年
SECOND_MICROSECOND	秒_微秒
MINUTE_MICROSECOND	分_微秒
MINUTE_SECOND	分_秒
HOUR_MICROSECOND	时间_微秒
HOUR_SECOND	小时_秒
HOUR_MINUTE	小时_分
DAY_MICROSECOND	天_微秒
DAY_SECOND	天_秒
DAY_MINUTE	天_分钟
DAY_HOUR	天_小时
YEAR_MONTH	年_月

4. Oracle

另外，在Oracle中，INTERVAL关键字还有专门的语法，可以起到MySQL中YEAR_MONTH关键字差不多的功能：

INTERVAL ‘integer [- integer]’ {YEAR | MONTH} [(precision)][TO {YEAR | MONTH}]

该数据类型常用来表示一段时间差，注意时间差只精确到年和月。precision为年或月的精确域, 有效范围是0到9，默认值为2。

比如：
INTERVAL ‘123-2’ YEAR(3) TO MONTH
表示：123年2个月， “YEAR(3)” 表示年的精度为3，可见"123"刚好为3为有效数值，如果该处YEAR(n)， n<3就会出错，注意默认是2。

INTERVAL ‘123’ YEAR
表示： 该处表示有错误，123精度是3了， 但系统默认是2，所以该处应该写成 INTERVAL ‘123’ YEAR(3) 或"3"改成大于3小于等于9的数值都可以的。