Mysql查询去年数据、上季度数据、上月数据、上周数据、昨天数据、

//Mysql查询去年数据、上季度数据、上月数据、上周数据、昨天数据、
BETWEEN DATE_SUB(concat(substring(#{dateTo}, 1, 8),'01'),	INTERVAL 2 MONTH) AND #{dateTo}
#上季度数据
select id from t_sys_material where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
#本季度数据
select id from t_sys_material where QUARTER(create_date)=QUARTER(now());
#上年数据
select id from t_sys_material where year(create_date)=year(date_sub(now(),interval 1 year));
#本年数据
select id from t_sys_material where YEAR(create_date)=YEAR(NOW());
#本月
select id from t_sys_material where DATE_FORMAT(create_date,'%Y%m') = DATE_FORMAT(CURDATE( ),'%Y%m');
#上一月
select id from t_sys_material where PERIOD_DIFF(date_format(now( ),'%Y%m') , date_format(create_date,'%Y%m')) =1;
#今天
select id from t_sys_material where to_days(create_date) = to_days(now());
#昨天
select id from t_sys_material where TO_DAYS(NOW( )) - TO_DAYS(create_date) <= 1;
#近7天
select id from t_sys_material where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_date);
#近30天
select id from t_sys_material where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_date);
#当前周数据 
select id from t_sys_material where YEARWEEK(date_format(create_date,'%Y-%m-%d')) = YEARWEEK(now());
#上周数据
select id from t_sys_material where  YEARWEEK(date_format(create_date,'%Y-%m-%d')) = YEARWEEK(now())-1;
#当前月份数据
select id from t_sys_material where date_format(create_date,'%Y-%m')=date_format(now(),'%Y-%m');
#距离现在6个月数据
select id from t_sys_material where create_date between date_sub(now(),interval 6 month) and now();
#上个月数据
select id from t_sys_material where date_format(create_date,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m');

select curdate();                       -- 获取当前日期
select last_day(curdate());                    -- 获取当月最后一天。
select DATE_ADD(curdate(),interval -day(curdate())+1 day);   -- 获取本月第一天
select date_add(curdate()-day(curdate())+1,interval 1 month); -- 获取下个月的第一天
select DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month ),DATE_ADD(curdate(),interval -day(curdate())+1 day)) from dual; -- 获取当前月的天数1
select date_add(date_add(curdate(),interval -day(curdate())+1 day),interval 1 month)-- 获取当前月的天数2