MySql 针对日期的一些计算/查询操作
废话不多说,直接上干货
-- 今天 SELECT * FROM t_user_info WHERE TO_DAYS(create_time) = TO_DAYS(NOW());
结果:
![](https://img2020.cnblogs.com/blog/1669659/202105/1669659-20210511101820705-1719291940.png)
-- 昨天
SELECT * FROM t_user_info WHERE TO_DAYS( NOW( ) ) - TO_DAYS( create_time) = 1;
结果:
![](https://img2020.cnblogs.com/blog/1669659/202105/1669659-20210511102144104-1038212338.png)
-- 近7天
SELECT * FROM t_user_info WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(create_time);
结果:
![](https://img2020.cnblogs.com/blog/1669659/202105/1669659-20210511102330154-1254815117.png)
-- 近30天
SELECT * FROM t_user_info WHERE DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= DATE(create_time);
结果:
![](https://img2020.cnblogs.com/blog/1669659/202105/1669659-20210511102427424-416777057.png)
-- 本月
SELECT * FROM t_user_info WHERE DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' );
-- 查询当前月份的数据
SELECT * FROM t_user_info WHERE DATE_FORMAT(create_time,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m');
结果:
![](https://img2020.cnblogs.com/blog/1669659/202105/1669659-20210511102546479-879311795.png)
-- 上一月
SELECT * FROM t_user_info WHERE PERIOD_DIFF( DATE_FORMAT( NOW() , '%Y%m' ) , DATE_FORMAT( create_time, '%Y%m' ) ) =1;
-- 查询上个月的数据
SELECT * FROM t_user_info WHERE DATE_FORMAT(create_time,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m');
结果:
![](https://img2020.cnblogs.com/blog/1669659/202105/1669659-20210511102757140-1155392322.png)
-- 查询当前这周的数据
SELECT * FROM t_user_info WHERE YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(NOW());
结果:
![](https://img2020.cnblogs.com/blog/1669659/202105/1669659-20210511103318155-1005291969.png)
-- 查询上周的数据
SELECT * FROM t_user_info WHERE YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d')) = YEARWEEK(NOW())-1;
结果:
-- 查询本季度数据
SELECT * FROM t_user_info WHERE QUARTER(create_time)=QUARTER(NOW());
-- 查询上季度数据
SELECT * FROM t_user_info WHERE QUARTER(create_time)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER));
-- 查询本年数据
SELECT * FROM t_user_info WHERE YEAR(create_time)=YEAR(NOW());
未完待续......