【MySQL】日期和时间函数（下）

1.时间和秒钟转换的函数

函数	小写	用法
TIME_TO_SEC(time)	time_to_sec	将time转化为秒并返回结果，公式：小时*3600+分钟*60+秒
SEC_TO_TIME	sec_to_time	将seconds转化为包含小时、分钟、秒的时间

SELECT TIME_TO_SEC(NOW());
 
SELECT SEC_TO_TIME(78774);

2.计算日期和时间的函数

第一组

函数	小写	用法
DATE_ADD(datetime,INTERVAL expr type)，ADDDATE(date,INTERVAL expr type)	date_add，addDate	返回与给定日期相差interval时间段的日期时间
DATE_SUB(date,INTERVAL expr type)，SUBDATE(date,INTERVAL expr type)	date_sub，subDate	返回与date相差interval时间间隔的日期

上述函数中type的取值

间隔类型（type取值）	小写	含义
HOUR	hour	小时
MINUTE	minute	分钟
SECOND	second	秒
YEAR	year	年
MONTH	month	月
DAY	day	日
YEAR_MONTH	year_month	年和月
DAY_HOUR	day_hour	日和小时
DAY_MINUTE	day_minute	日和分钟
DAY_SECOND	day_second	日和秒
HOUR_MINUTE	hour_minute	小时和分钟
HOUR_SECOND	hour_second	小时和秒
MINUTE_SECOND	minute_second	分钟和秒

示例1：

SELECT
	NOW(),                                  -- 2022-08-12 08:49:44
	DATE_ADD(NOW(),INTERVAL 1 DAY) AS col1,    -- 2022-08-13 08:49:44
 
	DATE_ADD('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col2,   -- 2021-10-21 23:32:13
	ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,    -- 2021-10-21 23:32:13
	DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,  -- 2021-10-21 23:33:13 	相差一分一秒
 
	DATE_ADD(NOW(),INTERVAL -1 YEAR) AS col5,            -- 2021-08-12 08:49:44
	DATE_ADD(NOW(),INTERVAL '1_1' YEAR_MONTH) AS col6    -- 2023-09-12 08:49:44  相差一年一个月
FROM DUAL;

示例2：

SELECT
	DATE_SUB('2021-04-21',INTERVAL 31 DAY) AS col1,     -- 2020-12-21 间隔31天
	SUBDATE('2021-04-21',INTERVAL 31 DAY) AS col2,      -- 2021-03-21
	DATE_SUB('2021-04-21 20:12:30',INTERVAL '1 1' DAY_HOUR) AS col3   -- 2021-04-20 19:12:30 间隔一天一小时
FROM DUAL;

第二组

函数	小写	用法
ADDTIME(time1,time2)	addTime	返回time1加上time2的时间。当time2为一个数字时，代表的是秒，可以为负数
SUBTIME(time1,time2)	subTime	返回time1减去time2后的时间。当time2为一个数字时，代表的是秒，可以为负数
DATEDIFF(date1,date2)	dateDiff	返回date1-date2的日期间隔天数
TIMEDIFF(time1,time2)	timeDiff	返回time1-time2的时间间隔
FROM_DAYS(N)	from_days	返回从0000年1月1日期，N天以后的日期
TO_DAYS(date)	to_days	返回日期date距离0000年1月1日的天数
LAST_DAY(date)	last_day	返回date所在月份的最后一天的日期
MAKEDATE(year,n)	makeDate	针对给定年份与所在年份中的天数返回一个日期
MAKETIME(hour,minute,second)	makeTime	将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(time,n)	period_add	返回time加上n后的时间

示例1：

SELECT
	NOW(),            -- 2022-08-12 09:25:25
	ADDTIME(NOW(),20),    -- 2022-08-12 09:25:45  加了20秒
	SUBTIME(NOW(),30),    -- 2022-08-12 09:24:55  减了30秒
	SUBTIME(NOW(),'1:1:3'),    -- 2022-08-12 08:24:22
 
	DATEDIFF(NOW(),'2021-10-01'),    -- 315 日期间隔天数
	TIMEDIFF(NOW(),'2021-10-25 22:10:10'),    -- 838:59:59 时间间隔
 
	FROM_DAYS(366),    -- 0001-01-01 从0000-01-01经过366天后的日期
	TO_DAYS('0000-12-25'),    -- 359  0000-12-25距离0000-01-01的天数
	LAST_DAY(NOW()),     -- 2022-08-31  该月最后一天
	MAKEDATE(YEAR(NOW()),12),    -- 2022-01-12  2022年经过12天
	MAKETIME(10,21,23),    -- 10:21:23  组合成时间
	PERIOD_ADD(20200101010101,10)  -- 20200101010111 加上10
FROM DUAL;
 

示例2：
查询7天内的新增用户有多少？
regist_time为用户注册时间。

SELECT COUNT(*) AS num 
FROM new_user 
WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7;
 

3.日期的格式化与解析

• 格式化：日期 ---> 字符串
• 解析： 字符串 ----> 日期

函数	小写	用法
DATE_FORMAT(date,fmt)	date_format	按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)	time_format	按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type)	get_format	返回日期字符串的显示格式
STR_TO_DATE(str,fmt)	str_to_date	按照字符串fmt对str进行解析，解析为一个日期

示例：

SELECT 
	DATE_FORMAT(NOW(),'%H:%i:%s'),    -- 11:05:12
	STR_TO_DATE('09/01/2009','%m/%d/%Y'),    -- 2009-09-01
	STR_TO_DATE('20140422154706','%Y%m%d%H%i%s'),    -- 2014-04-22 15:47:06
 
	GET_FORMAT(DATE, 'USA'),    -- %m.%d.%Y
	DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),    -- 08.12.2022
	STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d')    -- 2020-01-01
FROM DUAL;

fmt参数常用的格式符

fmt格式符	说明	fmt格式符	说明	fmt格式符	说明
%Y	4位数字表示年份	%y	两位数字表示年份
%M	月名表示月份（January,...）	%m	两位数字表示月份（01,02，03...）
%b	缩写的月名（Jan,Feb,...）	%c	数字表示月份（1,2,3,...）
%D	英文后缀表示月中的天数（1st,2nd,3rd,...）	%d	两位数字表示月中的天数（01,02,...）	%e	数字形式表示月中的天数（1,2,3,4,5...）
%H	两位数字表示小时，24小时制（01,02,...24）	%h和%l	两位数字表示小时，12小时制（01,02,...,12）
%k	数字形式的小时，24小时制（1,2,3,...,24）	%l	数字形式表示小时，12小时制（1,2,3,4...）
%i	两位数字表示分钟（00,01,...,59）	%S和%s	两位数字表示秒（00,01,...,59）
%W	一周中的星期名称（Sunday...）	%a	一周中的星期缩写（Sun,Mon,Tues,...）	%w	以数字表示周中的天数（0=Sunday，1=Monday...）
%j	以3位数字表示年中的天数（001,002,...）	%U	以数字表示年中的第几周（1,2,3,...）其中Sunday为周中的第一天	%u	以数字表示年中的第几周，（1,2,3...）其中Monday为周中第一天
%T	24小时制	%r	12小时制
%p	AM或PM	%%	表示%

GET_FORMAT函数中date_type和format_type参数取值：

日期类型	格式化类型	返回的格式化字符串
DATE	USA	%m.%d.%Y
DATE	JIS	%Y-%m-%d
DATE	ISO	%Y-%m-%d
DATE	EUR	%d.%m.%Y
DATE	INTERNAL	%Y%M%d
TIME	USA	%h:%i:%s%p
TIME	JIS	%H:%i:%s
TIME	ISO	%H:%i:%s
TIME	EUR	%H.%i.%s
TIME	INTERNAL	%H%i%s
DATETIME	USA	%Y-%m-%d %H.%i.%s
DATETIME	JIS	%Y-%m-%d %H:%i:%s
DATETIME	ISO	%Y-%m-%d %H:%i:%s
DATETIME	EUR	%Y-%m-%d %H.%i.%s
DATETIME	INTERNAL	%Y%m%d%H%i%s