Mysql8.0 中的日期函数

1. mysql中日期数据类型

mysql8.0 中共有5中日期数据类型，常用的是DATE、DATETIME、TIMESTAMP

类型	大小（bytes）	范围	格式	描述
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038	结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS

2. 日期函数

2.1 获取当前时间相关

函数	描述	示例	返回值
NOW()	返回当前日期和时间	SELECT NOW()；	2023-03-25 21:29:35
CURTIME()	返回当前时间	SELECT CURTIME()；	21:31:20
CURRENT_TIME()	返回当前时间	SELECT CURRENT_TIME()；	21:31:20
CURRENT_TIMESTAMP()	返回当前日期和时间	SELECTCURRENT_TIMESTAMP()；	2023-03-25 21:33:10
UNIX_TIMESTAMP()	返回从1970-01-01 00:00:00到当前毫秒值	select UNIX_TIMESTAMP()；	1679751341
CURDATE()	返回当前日期	SELECT CURDATE()；	2023-03-25
CURRENT_DATE()	返回当前日期	SELECT CURRENT_DATE()；	2023-03-25

2.2 日期抽取相关函数

函数	描述	示例	返回值
EXTRACT(type FROM d)	从日期 d 中获取指定的值，type 指定返回的值。type可取值为：MICROSECOND、SECOND、MINUTE HOUR 等	SELECT EXTRACT(MINUTE FROM ‘2011-11-11 11:12:11’)	12
YEAR()	返回年份	SELECT YEAR(“2017-06-15”)；	2017
MONTH(d)	返回月份	SELECT MONTH(‘2011-11-11 11:11:11’)；	11
DAY(d)	返回日期	SELECT DAY(“2017-06-15”)；	15
HOUR(d)	返回小时	SELECT HOUR(‘2011-11-12 13:14:15’)；	13
MINUTE(d)	返回分钟	SELECT MINUTE(‘2011-11-12 13:14:15’)；	14
SECOND(d)	返回秒	SELECT SECOND(‘2011-11-12 13:14:15’)；	15
QUARTER(d)	返回季度	SELECT QUARTER(‘2011-11-12 13:14:15’)；	4
MONTHNAME(d)	返回月份名称	SELECT MONTHNAME(‘2011-11-12 13:14:15’)；	November
DAYNAME(d)	返回d是星期几	SELECT DAYNAME(‘2011-11-12 13:14:15’)；	Saturday
DAYOFMONTH(d)	返回d是本月的第几天	SELECT DAYNAME(‘2011-11-12 13:14:15’)；	12
LAST_DAY(d)	返回给给定日期的那一月份的最后一天	SELECT LAST_DAY(‘2011-11-12 13:14:15’)；	2011-11-30
DAYOFWEEK(d)	返回d是星期几，1是星期日，2是星期1…	SELECT DAYOFWEEK(‘2011-11-12 13:14:15’)；	7
DAYOFYEAR(d)	返回d是当年的第几天	SELECT DAYOFYEAR(‘2011-11-12 13:14:15’)；	316
WEEK(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEK(‘2011-11-12 13:14:15’)；	15
WEEKDAY(d)	日期 d 是星期几，0 表示星期一，1 表示星期二	SELECT WEEKDAY(‘2011-11-12 13:14:15’)；	5
WEEKOFYEAR(d)	计算日期 d 是本年的第几个星期，范围是 0 到 53	SELECT WEEKOFYEAR(‘2011-11-12 13:14:15’)；	45
YEARWEEK(d)	返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推	SELECT YEARWEEK(‘2011-11-12 13:14:15’)；	201145

2.3 日期运算相关

函数	描述	示例	返回值
DATEDIFF(d1,d2)	计算日期 d1->d2 之间相隔的天数	SELECT DATEDIFF(‘2001-01-01’,‘2001-02-02’)	32
TIMEDIFF(time1, time2)	计算时间差	SELECT TIMEDIFF(“13:10:11”, “13:10:10”);	00:00:01
DATE_SUB(date,INTERVAL expr type)	函数从日期减去指定的时间间隔。	SELECT DATE_SUB(‘2011-11-12 13:14:15’,INTERVAL 2 DAY)	2011-11-10 13:14:15

计算起始日期d加上一个时间段后的日期

-- 语法：type 值可以是：SECOND_MICROSECOND、MINUTE_MICROSECOND、MINUTE_SECOND、HOUR_MICROSECOND、HOUR_SECOND、HOUR_MINUTE、DAY_MICROSECOND、DAY_SECOND、DAY_MINUTE、DAY_HOUR、YEAR_MONTH
ADDDATE/DATE_ADD(d，INTERVAL expr type)


-- 示例：
SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);    
-- 2017-06-25
 
SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);
-- 2017-06-15 09:49:21
 
SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);
-- 2017-06-15 06:34:21
 
SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);
-- 2017-04-15

其他相关日期计算函数使用方式均一致

2.4 时区相关函数

函数	描述	返回值
UTC_DATE()	返回UTC时区的日期	2023-03-25
UTC_TIME()	返回UTC时区的时间	14:41:10
UTC_TIMESTAMP()	返回UTC时区的日期和时间	2023-03-25 14:41:10
CONVERT_TZ()	从一个时区转到另一个时区	2023-03-25 22:41:10

2.5 日期格式化相关

2.5.1 将毫秒值时间戳转为指定格式日期

-- 语法：
FROM_UNIXTIME(BIGINT UNIXTIME[, STRING FORMAT])

-- 示例：
SELECT FROM_UNIXTIME(1598079966,'%Y-%m-%d %H:%i:%s'); 
-- 2020-08-22 15-06-06

2.5.2 按照指定的表达式显示日期

-- 语法：
DATE_FORMAT(d,f)

-- 示例：
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')
-- 2011-11-11 11:11:11 AM

2.5.3 将字符串转变为日期

-- 语法：
STR_TO_DATE(string, format_mask)

SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
-- 2017-08-10

3. 常见日期格式

format参数格式如下：

%a 缩写星期名
%b 缩写月名
%c 月，数值
%D 带有英文前缀的月中的天
%d 月的天，数值(00-31)
%e 月的天，数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟，数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%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) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天，与 %X 使用
%v 周 (01-53) 星期一是一周的第一天，与 %x 使用
%W 星期名
%w 周的天 （0=星期日, 6=星期六）
%X 年，其中的星期日是周的第一天，4 位，与 %V 使用
%x 年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y 年，4 位
%y 年，2 位