MySQL 中的日期和时间函数

MySQL日期和时间函数：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

建表

drop table if exists shijian;
CREATE TABLE shijian(
    id int(11) primary key auto_increment,
	name varchar(20),
    bir1 int(20) comment "int时间",
    bir2 varchar(20) comment "var时间",
    bir3 date comment "date时间",
    bir4 datetime comment "datetime时间"
)DEFAULT CHARSET=UTF8;


INSERT INTO shijian VALUES(1,'赵四',20220304,'2022-03-04','2022-03-04','2022-03-04 09:11:31');
INSERT INTO shijian VALUES(2,'钱风',20211214,'2021-12-14','2021-12-14','2021-12-14 18:32:22');
INSERT INTO shijian VALUES(3,'孙磊',20210613,'2021-06-13','2021-06-13','2021-06-13 08:20:02');
INSERT INTO shijian VALUES(4,'李云',20220101,'2022-01-01','2022-01-01','2022-01-01 10:12:24');
INSERT INTO shijian VALUES(5,'周洲',20201011,'2020-10-11','2020-10-11','2020-10-11 16:44:45');

获取时间

CURDATE(), CURRENT_DATE(), NOW(), CURTIME()

• curdate()和current_date() ：获取当前日期函数；
• now()：返回服务器的当前日期和时间；
• sysdate()：当前的系统日期和时间；
• curtime(）：返回当前时间，只包含时分秒；
• 可通过curdate()+0，将当前日期转换成数值；
• 可通过now()+0，将当前时间转换成数值；

SELECT CURDATE(), CURRENT_DATE(), NOW() , CURTIME(), CURDATE()+0, NOW()+0

YEAR(), MONTH(), DAY() …

选取日期时间的各个部分：日期、时间、年、季度、月、日、小时、分钟、秒、微秒（常用）

SELECT NOW(), DATE(NOW()); 			-- 日期  输出：2022-03-09 14:24:48 	2022-03-09
SELECT NOW(), TIME(NOW()); 			-- 时间  输出：2022-03-09 14:24:48	14:24:48
SELECT NOW(), YEAR(NOW());			-- 年    输出：2022-03-09 14:24:48	2022
SELECT NOW(), QUARTER(NOW()); 		-- 季度  输出：2022-03-09 14:24:48 	1
SELECT NOW(), MONTH(NOW());		 	-- 月    输出：2022-03-09 14:24:48 	3
SELECT NOW(), WEEK(NOW()); 			-- 周    输出：2022-03-09 14:24:48 	10
SELECT NOW(), DAY(NOW()); 			-- 日    输出：2022-03-09 14:24:48 	9
SELECT NOW(), HOUR(NOW()); 			-- 小时  输出：2022-03-09 14:24:48 	14
SELECT NOW(), MINUTE(NOW());		-- 分钟  输出：2022-03-09 14:24:48 	24
SELECT NOW(), SECOND(NOW());	   	-- 秒    输出：2022-03-09 14:24:48 	48
SELECT NOW(), MICROSECOND(NOW()); 	-- 微秒  输出：2022-03-09 14:24:48 	0

EXTRACT()

EXTRACT(unit FROM date)
函数用于返回日期/时间的单独部分，比如年、月、日、小时、分钟等等。

date 参数是合法的日期表达式。unit 参数见附表A。

SELECT EXTRACT(YEAR FROM  '2022-02-12 16:31:12')
SELECT EXTRACT(YEAR_MONTH FROM  '2022-02-12 16:31:12')
SELECT EXTRACT(HOUR_MINUTE FROM   '2022-02-12 16:31:12')

>>输出结果
2022
202202
1631

DATEDIFF（时间差）

DATEDIFF(expr1, expr2)：返回两个日期相减（expr1 − expr2 ）相差的天数

SELECT DATEDIFF(20220204 ,20220301)
SELECT DATEDIFF('2022-03-04' ,'2021-03-01')

>>输出结果
-25			
368				

TIMESTAMPDIFF() （时间差函数）

TIMESTAMPDIFF(unit, start_time, end_time)
TIMESTAMPDIFF函数返回 end_time - start_time 的结果，其中start_time和end_time是时间的表达式。TIMESTAMPDIFF函数允许其参数具有混合类型。
unit参数是确定(END-BEGIN)的结果的单位，表示为整数。 以下是有效单位：

YEAR MONTH DAY HOUR MINUTE SECOND MICROSECOND QUARTER WEEK
年 月 日 时 分 秒 毫秒 季度 周

-- 时间差函数：timestampdiff
SELECT TIMESTAMPDIFF(YEAR,"2020-02-07",NOW())  -- 1
SELECT TIMESTAMPDIFF(QUARTER,"2020-02-27",NOW())  -- 4
SELECT TIMESTAMPDIFF(MONTH,"2020-02-27",NOW())  -- 12
SELECT TIMESTAMPDIFF(WEEK,"2020-02-27",NOW())  -- 52
SELECT TIMESTAMPDIFF(DAY,"2020-02-07",NOW())  -- 366
SELECT TIMESTAMPDIFF(HOUR,"2020-02-27",NOW())  -- 8800
SELECT TIMESTAMPDIFF(MINUTE,"2020-02-27",NOW())  -- 528010
SELECT TIMESTAMPDIFF(SECOND,"2020-02-27",NOW())  -- 31680773

DATE_ADD(), DATE_SUB() （时间的加减）

• DATE_ADD(date, INTERVAL expr unit)
• DATE_SUB(date, INTERVAL expr unit)
• interval是间隔类型关键字
• expr是一个表达式，对应后面的类型
• unit是时间间隔的单位(间隔类型)。unit 参数见附表A

SELECT DATE_ADD('2022-02-21', INTERVAL 12 YEAR),  DATE_SUB('2022-02-23', INTERVAL 13 YEAR);
SELECT DATE_ADD('2022-02-21', INTERVAL 12 MONTH), DATE_SUB('2022-02-23', INTERVAL 13 MONTH);
SELECT DATE_ADD('2022-02-21', INTERVAL 12 DAY),   DATE_SUB('2022-02-23', INTERVAL 13 DAY);

>>输出结果
2034-02-21			2009-02-23
2023-02-21			2021-01-23
2022-03-05			2022-02-10

DAYOFWEEK(), DAYOFMONTH(), DAYOFYEAR() （日期在一周、一月、一年中是第几天）

一周中的第一天是周日，第二天是周一，第三天是周二，，，，

SELECT DAYOFWEEK('2022-02-21');
SELECT DAYOFMONTH('2022-02-21');
SELECT DAYOFYEAR('2022-02-21');

>>输出结果
2
21
52

LAST_DAY() （月底日期）

LAST_DAY() ：返回当月的最后一天的日期

SELECT LAST_DAY(20220208)
SELECT LAST_DAY('2020-02-11')

>>输出结果
2020-02-11
2020-02-11

DATE_FORMAT(), TIME_FORMAT() （格式转换）

将日期和时间格式化的函数：

• DATE_FORMAT(date,format)
• TIME_FORMAT(time,format)

根据format 指定的格式显示日期或者时间值。
date 参数是合法的日期
format 规定日期/时间的输出格式。 参数见 附表B

SELECT DATE_FORMAT('2022-02-21', '%Y<>%M<>%D') ;
SELECT DATE_FORMAT(20220221090803, '%Y<>%M<>%D<>%a') ;
SELECT TIME_FORMAT(20220221090803, '%H<>%i<>%s') ;

>>输出结果
2022<>February<>21st
2022<>February<>21st<>Mon
09<>08<>03

FROM_UNIXTIME(), UNIX_TIMESTAMP() （时间戳转换）

• from_unixtime(bigint unixtime, string format)     把时间戳转换为指定格式的日期
• unix_timestamp(string date)     把日期转换为时间戳

format 中的参数见附表B

SELECT FROM_UNIXTIME(1646811682, '%Y-%m-%d %H:%i:%S');
SELECT UNIX_TIMESTAMP('2022-03-09 15:41:40');

>>输出结果
2022-03-09 15:41:22
1646811700

-- hivesql使用函数时略有不同，但这两个函数在oracle中无法执行 
select from_unixtime(1646811700, 'yyyyMMddHHmmss')  --20220309154140
select from_unixtime(1646811700, 'yyyy-MM-dd')  --2022-03-09
select unix_timestamp('20220309','yyyyMMdd') --1646755200
select unix_timestamp('20220309154140','yyyyMMddHHmmss')  --1646811700
select unix_timestamp('2022-03-09 15:41:40', 'yyyy-MM-dd HH:mm:ss')  --1646811700 

-- oracle时间戳转日期
select 1646811700/(60*60*24) + to_date('1970-01-01 08:00:00','yyyy-MM-dd hh24:mi:ss')  from dual;  --2022/3/9 15:41:40
-- oracle日期转时间戳
select (
to_date('2022-03-09 15:41:40','yyyy-mm-dd hh24:mi:ss')-to_date('1970-01-01 08:00:00','yyyy-mm-dd hh24:mi:ss')
		) * 86400 from dual;  --1646811700

-- mysql转格式
SELECT DATE_FORMAT('20220309','%Y-%m-%d') 
-- hivesql转格式
select date_format('20220309','yyyy-MM-dd')

总结

在使用这些函数时，时间的格式可以是 int varchar date datetime 的任意一种，所以适用性还是很高的。虽然开头建的表没用上。。。

附表A

unit	说明	unit	说明	unit	说明	unit	说明	unit	说明
YEAR	年	HOUR	时	QUARTER	季度	DAY_SECOND	日和秒	DAY_MICROSECOND	日和毫秒
MONTH	月	MINUTE	分	YEAR_MONTH	年和月	HOUR_MINUTE	小时和分钟	HOUR_MICROSECOND	小时和毫秒
DAY	日	SECOND	秒	DAY_HOUR	日和小时	HOUR_SECOND	小时和秒	MINUTE_MICROSECOND	分钟和毫秒
WEEK	周	MICROSECOND	毫秒	DAY_MINUTE	日和分钟	MINUTE_SECOND	分钟和秒	SECOND_MICROSECOND	秒和毫秒

附表B

%a 缩写的星期名字(Sun……Sat)
%b 缩写的月份名字(Jan……Dec)
%c 月, 数字(1……12)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%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 月名字(January……December)
%m 月, 数字(01……12)
%p AM或PM
%r 时间,12 小时(hh:mm:ss [AP]M)
%S 秒(00……59)
%s 秒(00……59)
%T 时间,24 小时(hh:mm:ss)
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一天
%V 星期(01……53), 这里星期天是星期的第一天，与%X使用
%v 星期(01……53), 这里星期一是星期的第一天，与%x使用
%W 星期名字(Sunday……Saturday)
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
%X 年, 这里星期天是星期的第一天，4 位，与%V使用
%x 年, 这里星期一是星期的第一天，4 位，与%v使用
%Y 年, 数字, 4 位
%y 年, 数字, 2 位