mysql 查询今天、昨天、本周、上周、本月、上月、本季度、上季度、本年、上一年、的数据

1、今天

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

2、昨天

select * from 表名 where to_days( now( ) ) - to_days( 时间字段名) <= 1

3、近7天

select * from 表名 where date_sub(curdate(), interval 7 day) <= date(时间字段名)

4、当前这周

select name,submittime from enterprise where yearweek(date_format(submittime,'%y-%m-%d')) = yearweek(now())

5、上周

select name,submittime from enterprise where yearweek(date_format(submittime,'%y-%m-%d')) = yearweek(now())-1

6、近30天

select * from 表名 where date_sub(curdate(), interval 30 day) <= date(时间字段名)

7、本月

select * from 表名 where date_format( 时间字段名, '%y%m' ) = date_format( curdate( ) , '%y%m' )

8、上一月

select * from 表名 where period_diff( date_format( now( ) , '%y%m' ) , date_format( 时间字段名, '%y%m' ) ) =1

9、查询当前半年的数据 

select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now()

10、本季度

select * from `ht_invoice_information` where quarter(create_date)=quarter(now())

11、上季度

select * from `ht_invoice_information` where quarter(create_date)=quarter(date_sub(now(),interval 1 quarter))

12、本年

select * from `ht_invoice_information` where year(create_date)=year(now())

13、上年

select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year))