MySQL按时间统计数据的方法总结

//查询一周
格式：select * from 表名称 where date_sub(curdate(), interval 6 day) <= date(表内时间字段);
语句：select * from garbage_collect  where date_sub(curdate(), interval 6 day) <= date(collection_time);

//查询一个月
 格式：select * from 表名称 where date_sub(curdate(), interval 1 month) <= date(表内时间字段);
 语句：select * from garbage_collect  where date_sub(curdate(),  interval 1 month) <= date(collection_time);

//查询近半年
格式：select * from 表名称 where date_sub(curdate(),  interval 6 month) <= date(表内时间字段);
语句：select * from garbage_collect  where date_sub(curdate(),  interval 6 month) <= date(collection_time);

//查询2020年的数据：
格式：select * from 表名称 where year(表名称.表字段)="某年" 
语句：select * from garbage_collect where year(garbage_collect.collection_time)="2020" 
//查询9月份的数据：
格式：select * from 表名称 where year(表名称.表字段)="某月" 
语句：select * from garbage_collect where month(garbage_collect.collection_time)="09" 
//查询时期是08的数据：
格式：select * from 表名称 where year(表名称.表字段)="某日" 
语句：select * from garbage_collect where day(garbage_collect.collection_time)="08" 
//查询2020年03月份的数据：
select * from garbage_collect where year(garbage_collect.collection_time)="2020" and month(garbage_collect.collection_time)="03"
//查询2020年03月06日的数据：
select * from garbage_collect where year(garbage_collect.collection_time)="2020" and month(garbage_collect.collection_time)="03" and day(garbage_collect.collection_time)="06";

格式：select * from 表名称 where 表字段 between "开始时间" and "结束时间";
语句：select * from garbage_collect where collection_time  between "2020-02-01" and "2020-03-31";

//查询指定时间段数据（开始时间-结束时间）包含结束时间
语句:select * from garbage_collect where  collection_time  between "2020-03-17" and date_add("2020-03-17",interval 1 day)