Fork me on GitHub

MySQL根据日期查询

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

今天

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

昨天

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

本周

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

上周

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

近7天

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

近30天

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

本月

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

上一个月

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

本季度

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

上季度

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

本年

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

去年

1
select * from 'ht_invoice_information' where year(create_date)= year(date_sub(now(), interval 1 year));
Your support will encourage me to continue to create!