mysql查询今天、昨天、7天、近30天、本月、上一月 数据

今天

select*from表名whereto_days(时间字段名)=to_days(now());

昨天

SELECT*FROM表名WHERETO_DAYS( NOW( ) )-TO_DAYS( 时间字段名)<=1

7天

SELECT*FROM表名whereDATE_SUB(CURDATE(), INTERVAL7DAY)<=date(时间字段名)

近30天

SELECT*FROM表名whereDATE_SUB(CURDATE(), INTERVAL30DAY)<=date(时间字段名)

本月

SELECT*FROM表名WHEREDATE_FORMAT( 时间字段名,'%Y%m')=DATE_FORMAT( CURDATE( ) ,'%Y%m')

上一月

SELECT*FROM表名WHEREPERIOD_DIFF( date_format( now( ) ,'%Y%m') , date_format( 时间字段名,'%Y%m') )=1

#查询本季度数据

select*from`ht_invoice_information`whereQUARTER(create_date)=QUARTER(now())

#查询上季度数据

select*from`ht_invoice_information`whereQUARTER(create_date)=QUARTER(DATE_SUB(now(),interval1QUARTER));

#查询本年数据

select*from`ht_invoice_information`whereYEAR(create_date)=YEAR(NOW());

#查询上年数据

select*from`ht_invoice_information`whereyear(create_date)=year(date_sub(now(),interval1year));

查询当前这周的数据 

SELECTname,submittimeFROMenterpriseWHEREYEARWEEK(date_format(submittime,'%Y-%m-%d'))=YEARWEEK(now());

查询上周的数据

SELECTname,submittimeFROMenterpriseWHEREYEARWEEK(date_format(submittime,'%Y-%m-%d'))=YEARWEEK(now())-1;

查询当前月份的数据

selectname,submittimefromenterprisewheredate_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

查询距离当前现在6个月的数据

selectname,submittimefromenterprisewheresubmittimebetweendate_sub(now(),interval6month)andnow();