1、首先新建一张带时间字段的测试表:tb_test

CREATE TABLE `tb_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_name` varchar(255) DEFAULT NULL, `password` varchar(255) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2、接着插入几条测试数据

insert into `tb_test` ( `user_name`, `password`, `create_time`) values ( '周杰伦', '123456', '2018-11-14 21:43:27');insert into `tb_test` ( `user_name`, `password`, `create_time`) values ( 'jay', '654321', '2018-10-08 21:43:42');insert into `tb_test` ( `user_name`, `password`, `create_time`) values ( '五月天', '1', '2018-11-13 21:49:27');insert into `tb_test` ( `user_name`, `password`, `create_time`) values ( '薛之谦', '2', '2017-11-08 21:53:00');insert into `tb_test` ( `user_name`, `password`, `create_time`) values ( '林俊杰', '1', '2018-11-14 21:53:33');insert into `tb_test` ( `user_name`, `password`, `create_time`) values ( 'bb', '1', '2018-11-08 21:54:31');

3、相关时间结果查询如下

查询今天

select * from tb_test where to_days(create_time) = to_days(now());

查询昨天

SELECT * FROM tb_test WHERE TO_DAYS(NOW() ) - TO_DAYS(create_time) = 1;

查询近7天

SELECT * FROM tb_test where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_time);

查询当前这周的数据

SELECT * FROM tb_test WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now());

查询上周的数据

SELECT * FROM tb_test WHERE YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now())-1;

查询近30天

SELECT * FROM tb_test where DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date(create_time);

查询本月

SELECT * FROM tb_test WHERE DATE_FORMAT(create_time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');

查询上一月

SELECT * FROM tb_test WHERE PERIOD_DIFF(date_format(now(),'%Y%m'),date_format(create_time,'%Y%m')) =1;

查询距离当前现在6个月的数据

select * from tb_test where create_time between date_sub(now(),interval 6 month) and now();

查询本季度数据

select * from tb_test where QUARTER(create_time)=QUARTER(now());

查询上季度数据

select * from tb_test where QUARTER(create_time)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

查询本年数据

select * from tb_test where YEAR(create_time)=YEAR(NOW());

查询上年数据

select * from tb_test where YEAR(create_time)=YEAR(date_sub(now(),interval 1 year));

最后查询全表

select * from tb_test;

如果有没有涉及时间相关查询的的欢迎评论，我会一一在评论中补充。

以上是本次分享的史上最全的mysql时间相关查询总结，分享学习，分享收货，喜欢的请帮忙点赞、转发，谢谢。

史上最全的mysql时间相关查询总结