sql日期相关处理

查询本周记录
select * from `tableName` where WEEKOFYEAR(date_field)=WEEKOFYEAR(NOW());
#查询上周记录
select * from `tableName` where date_field>=date_add(now(),interval -(8 + weekday(now())) day)
and date_field<=date_add(now(),interval -(1 + weekday(now())) day);

#或者

select * from `tableName` where WEEKOFYEAR(date_field)=WEEKOFYEAR(DATE_SUB(now(),INTERVAL 1 week));

//前一天
select date_sub(now(),interval 1 day);

Select * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)

近30天前

//时间转成年月日
select date_format(now(),'%Y%m%d')

本月

Select * FROM 表名 Where DATE_FORMAT( 时间字段名, ‘%Y%m’ ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m’ )

上一月

Select * FROM 表名 Where PERIOD_DIFF( date_format( now( ) , ‘%Y%m’ ) , date_format( 时间字段名, ‘%Y%m’ ) ) =1

查询当前月份的数据
select name,submittime from enterprise   where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

查询当前这周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());
 
查询上周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

近30天前

Select * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)

#查询上月数据
select * from `tableName` where date_field<=last_day(date_add(now(),interval -1 MONTH))
and date_field>=DATE_FORMAT(concat(extract(year_month from date_add(now(),interval -1 MONTH)),'01'),'%Y-%m-%d');

#或者

select * from `tableName` where MONTH(date_field)=MONTH(DATE_SUB(NOW(),interval 1 month))
and year(date_field)=year(now());