关于MySQL中日期的使用

一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第3天，点击查看活动详情。

最近在使用sql函数时，因项目数据库不同，导致sql中日期的使用方法大不相同， 一些常见的日期计算，都有不同的应用，故整理记录一下MySQL的日期用法。

以user表为例

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  `crt_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 添加基础数据
INSERT INTO `user`.`user`(`id`, `name`, `age`, `email`, `crt_time`) VALUES (1, 'Jone', 18, 'test1@baomidou.com', '2022-04-01 22:20:02');
INSERT INTO `user`.`user`(`id`, `name`, `age`, `email`, `crt_time`) VALUES (2, 'Jack', 20, 'test2@baomidou.com', '2022-04-02 22:20:07');
INSERT INTO `user`.`user`(`id`, `name`, `age`, `email`, `crt_time`) VALUES (3, 'Tom', 28, 'test3@baomidou.com', '2022-04-03 22:20:11');
INSERT INTO `user`.`user`(`id`, `name`, `age`, `email`, `crt_time`) VALUES (4, 'Sandy', 21, 'test4@baomidou.com', '2022-04-04 22:20:16');
INSERT INTO `user`.`user`(`id`, `name`, `age`, `email`, `crt_time`) VALUES (5, 'Billie', 24, 'test5@baomidou.com', '2022-04-05 22:20:21');
INSERT INTO `user`.`user`(`id`, `name`, `age`, `email`, `crt_time`) VALUES (6, 'Michale', 12, 'test6@baom', '2022-04-06 22:20:27');

1 日期等于\大于\小于

常见的日期等于\大于\小于查询， 直接将前端传入日期与数据库字段比较。

-- 时间大于 '2022-04-02 22:20:07'
select * FROM `user` WHERE  crt_time > '2022-04-02 22:20:07'

-- 时间等于 '2022-04-02 22:20:07'
select * FROM `user` WHERE  crt_time = '2022-04-02 22:20:07'

-- 时间小于 '2022-04-02 22:20:07'
select * FROM `user` WHERE  crt_time < '2022-04-02 22:20:07'

2 日期增加一个时间段 date_add()

-- now()方法表示当前日期
select NOW();  -- 2022-04-08 22:59:37

-- date_add() 函数时给当前日期添加一个时间段
-- 当前日期加一天
select date_add(NOW(), interval 1 day); 

-- 当前日期加一小时
select date_add(NOW(), interval 1 hour); 

-- 当前日期加一分钟
select date_add(NOW(), interval 1 minute);

-- 综上 可知道日期添加规律
-- 当前日期加一星期
select date_add(NOW(), interval 1 week);

-- 当前日期加一月
select date_add(NOW(), interval 1 month);

-- 当前日期加一年
select date_add(NOW(), interval 1 year);

-- 当前日期加负一年  即减一年
select date_add(NOW(), interval -1 year);

3 日期减小一个时间段 date_sub()

和上述用法类似

-- date_sub() 函数时给当前日期减小一个时间段
-- 当前日期减一天
select date_sub(NOW(), interval 1 day); 

-- 当前日期减一小时
select date_sub(NOW(), interval 1 hour); 

-- 当前日期减一分钟
select date_sub(NOW(), interval 1 minute);

-- ......
-- 当前日期减负一年  即加一年
select date_sub(NOW(), interval -1 year);

4 两个日期相差比较

• datediff(date1,date2)函数: 将data1减去date2,返回相差的天数

• timediff(time1,time2)函数: 将time1减去time2,返回相差的时间数

-- 相差 一天   1
select datediff('2022-04-02 22:20:27', '2022-04-01 22:20:02'); 

-- 相差一天零25秒  24:00:25
select timediff('2022-04-02 22:20:27', '2022-04-01 22:20:02'); 

5 时间截取

• DATE_FORMAT(date, format)函数:将date时间按照指定格式截取

常见的是截取日期的年月日,抛弃后面时分秒 进行比较

-- 现在时间  2022-04-08 23:23:48
select NOW();

-- 现在截取年月日时间  2022-04-08
select DATE_FORMAT(NOW(), '%Y-%m-%d') 

6 常见小案例

如查询小于当前时间三天的数据

select DATE_FORMAT(crt_time, '%Y-%m-%d') ,  DATE_FORMAT(date_sub(NOW(), interval 3 day), '%Y-%m-%d')
from `user`
where  DATE_FORMAT(crt_time, '%Y-%m-%d') < DATE_FORMAT(date_sub(NOW(), interval 3 day), '%Y-%m-%d') ; 

-- 2022-04-01	2022-04-05
-- 2022-04-02	2022-04-05
-- 2022-04-03	2022-04-05
-- 2022-04-04	2022-04-05