msyql关于时间的一些查询，年、月、季度

--今天
SELECT * FROM `user` where TO_DAYS(createTime)=TO_DAYS(NOW()) ;
--近7天
SELECT * FROM  user where DATE_SUB(CURDATE(),INTERVAL 7 DAY)<=DATE(createTime)
--近30天
SELECT * FROM user where DATE_SUB(CURDATE(),INTERVAL 30 DAY)<=DATE(createTime)
--本月
SELECT * FROM user where DATE_FORMAT(createTime,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m')
--距离本月一月
SELECT * FROM user where PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'),DATE_FORMAT(createTime,'%Y%m'))=1
--查询距离当前现在6个月的数据
select * from user where createTime between date_sub(now(),interval 6 month) and now();
--查询上个月的数据
select * from user where date_format(createTime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
--查询本季度数据
SELECT * from user where QUARTER(createTime)=QUARTER(NOW())
--查询上季度的数据
SELECT * from user where QUARTER(createTime)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER))
--查询本年的数据
SELECT * from user where YEAR(createTime)=YEAR(NOW())
--查询上一年的数据
SELECT * from user where YEAR(createTime)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))
--查询当前周的数据
SELECT * FROM user where YEARWEEK(DATE_FORMAT(createTime,'%Y-%m-%d'))=YEARWEEK(NOW())
--查询上一周的数据
SELECT * from user where YEARWEEK(DATE_FORMAT(createTime,'%Y-%m-%d'))=YEARWEEK(NOW())-1