MySQL 中的日期和时间函数
建表
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()：返回服务器的当前日期和时间；
- 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), 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
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(1646811502, '%Y-%m-%d %H:%i:%S');
SELECT UNIX_TIMESTAMP('2022-03-09 15:41:40');
>>输出结果
2022-03-09 15:38:22
1646811700
总结
在使用这些函数时，时间的格式可以是 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 位