MySQL日期函数

SELECT
	DATE( '2022-08-20' ),
	DATE( '2022-08-20 12:12:11' ),
	DATE( '2022/05/20' ),
	DATE( '2022-05-20T10:20:33' ) 
FROM DUAL;
# 2022-08-20, 2022-08-20, 2022-05-20, 2022-05-20

1.2 DATE_FORMAT

语法

DATE_FORMAT(date,format)

示例

SELECT DATE_FORMAT('2022-08-20','%Y/%m/%d'); # 2022/08/20

SELECT DATE_FORMAT('2022-08-20','%Y%m%d');   # 20220820

SELECT DATE_FORMAT('2022-08-20','%Y');       # 2022

1.3 ADDDATE, DATE_ADD

语法

ADDDATE(date,INTERVAL expr unit), DATE_ADD(date,INTERVAL expr unit)

示例

# ADDDATE
SELECT ADDDATE('2021-01-01', INTERVAL 1 DAY) FROM DUAL; # 2021-01-02

SELECT ADDDATE('2021-01-31',INTERVAL 1 MONTH) FROM DUAL; # 2021-02-28

SELECT ADDDATE('2021-01-31',INTERVAL 1 YEAR) FROM DUAL;  # 2022-01-31


# DATE_ADD
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);

1.4 DATE_SUB

语法

DATE_SUB(date,INTERVAL expr unit)

示例

SELECT DATE_SUB('2021-03-31',INTERVAL 1 DAY);    # 2021-03-30

SELECT DATE_SUB('2021-03-31',INTERVAL 1 MONTH);  # 2021-02-28

SELECT DATE_SUB('2021-03-31',INTERVAL 1 YEAR);   # 2020-03-31

1.5 DATEDIFF

语法

DATEDIFF(expr1,expr2)

示例

SELECT DATEDIFF('2022-08-20','2021-08-19'); # 366

SELECT DATEDIFF('2022-08-20','2022-08-19'); # 1

1.6 CURDATE(), CURRENT_DATE, CURRENT_DATE()

语法

CURDATE(), CURRENT_DATE, CURRENT_DATE()

示例

select CURDATE(), CURRENT_DATE, CURRENT_DATE() FROM DUAL
# 2022-08-20, 2022-08-20, 2022-08-20

2 日期时间函数

2.1 CURTIME(),CURRENT_TIME

语法

CURTIME(),CURRENT_TIME

示例

mysql> SELECT CURTIME(),CURRENT_TIME
-> '14:59:28',  '14:59:28'

2.2 ADDTIME

语法

ADDTIME(expr1,expr2)

将 expr1 和 expr2 表示的时间值相加

示例

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'

2.3 TIME

语法

TIME(expr)

提取日期时间中的时间部分

示例

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'

2.4 TIMEDIFF

语法

TIMEDIFF(expr1,expr2)

返回 expr1 − expr2 运算结果表示的一个时间值

示例

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'

2.5 TIME_TO_SEC

语法

TIME_TO_SEC(time)

将时间表示的值转为秒数

示例

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

2.6 SEC_TO_TIME

语法

SEC_TO_TIME(seconds)

函数功能

将指定的秒数转换成time格式(hh:mm:ss)

示例

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

2.7 TIMESTAMP

语法

TIMESTAMP(expr ) , TIMESTAMP( expr1 , expr2 )

函数功能

将指定日期或者日期时间表示成datetime格式

参数说明：

当只有一个参数时

直接格式化即可，参数必须是date或者datetime格式

当函数有两个参数时

先将两个参数值相加，然后表示成datetime时间格式。

参数1必须是date或者datetime，参数2必须是time格式(不能含有date部分)

当传入的参数不满足上面的约束时，会出现奇怪结果

示例

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'

2.8 TIMESTAMPDIFF

语法

TIMESTAMPDIFF(unit , datetime_expr1 , datetime_expr2 )

函数功能

两个日期时间相差的指定unit时间单位数

unit单位可取的值

MICROSECOND (microseconds), 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

2.9 TIMESTAMPADD

语法

TIMESTAMPADD(unit , interval , datetime_expr )

函数功能

将指定unit时间单位数加到 datetime_expr表示的时间上

示例

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'

2.10 UNIX_TIMESTAMP

语法

UNIX_TIMESTAMP([date])

返回指定日期时间的时间戳

示例

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

2.11 FROM_UNIXTIME

语法

FROM_UNIXTIME( unix_timestamp [, format ])

函数功能

将时间戳转换成指定格式的datetime格式

示例

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'

2.12 EXTRACT

语法

EXTRACT( unit FROM date )

函数功能

从datetime日期时间值里面抽取指定的unit时间单位部分

此处的unit时间单位值参考附录中【Temporal Interval 表达式】

示例

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

2.13 datetime各部位函数

示例

SELECT SECOND('2022-08-20 20:01:30') # 30
SELECT SECOND('20:01:24') # 24

SELECT MINUTE('2022-08-20 20:01:30') # 1
SELECT MINUTE('20:02:24') # 2

SELECT HOUR('2022-08-20 20:01:30') # 20
SELECT HOUR('09:02:24') # 2

SELECT DAY('2022-08-12 20:01:30') # 12
SELECT DAY('2022-08-12') # 12

SELECT MONTH('2022-08-12 20:01:30') # 8
SELECT MONTH('2022-08-12') # 12 # 8

SELECT YEAR('2022-08-12 20:01:30') # 2022
SELECT YEAR('2022-08-12') # 2022

2.14 UTC 时间相关函数

示例

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

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

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

3 日期时间格式设置函数

格式说明参见附录

3.1 DATE_FORMAT

语法

DATE_FORMAT(date,format)

示例

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'

3.2 TIME_FORMAT

语法

TIME_FORMAT(time,format)

示例

SELECT TIME_FORMAT('100:00:00', '%H %k %h %I %l'); # 100 100 04 04 4

SELECT TIME_FORMAT('23:00:00', '%H %k %h %I %l') # 23 23 11 11 11

SELECT TIME_FORMAT('08:00:00', '%H %k %h %I %l') # 08 8 08 08 8

3.3 STR_TO_DATE

语法

STR_TO_DATE(str,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'

# 字符串开头有干扰字符，则格式里面也要表明干扰字符，否则结果为NULL。结尾干扰字符不受影响
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'

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'


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'

3.4 FROM_UNIXTIME

语法

FROM_UNIXTIME( unix_timestamp [, format ])

函数功能

将时间戳转换成指定格式的datetime格式

示例

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'