mysql获取当前时间的前一天

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、接着插入几条测试数据

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

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

查询今天

ｓｅｌｅｃｔ * from tb_test where to_days(create_time) = to_days(now());

查询昨天

ｓｅｌｅｃｔ * FROM tb_test WHERE TO_DAYS(NOW() ) - TO_DAYS(create_time) = 1;

查询近7天

ｓｅｌｅｃｔ * FROM tb_test where DATE_SUB(CURDATE(), INTERVAL 7 DAY)