前言:
如今大家对“mybatismysql时间”大体比较关注,咱们都需要知道一些“mybatismysql时间”的相关文章。那么小编也在网上汇集了一些有关“mybatismysql时间””的相关文章,希望各位老铁们能喜欢,我们一起来学习一下吧!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时间相关查询总结,分享学习,分享收货,喜欢的请帮忙点赞、转发,谢谢。
标签: #mybatismysql时间