MySQL根据日期查询

MySQL根据日期查询。此处并没有考虑性能问题。

今天

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

昨天

select * from 表名 where datediff(字段，now())= -1;

本周

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

上周

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

近7天

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

近30天

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

本月

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 * from 'ht_invoice_information' where quarter(create_date)=quarter(now());

上季度

select * from 'ht_invoice-information' where quarter(create_date)=quarter(date_sub(now(), interval 1 quarter));

本年

select * from 'ht_invoice_information' where year(create_date)=year(now());

去年

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