❤️mysql查询当前时间一个月以内的数据
mysql 查询当前时间一个月以内的数据并按日期排序
SELECT
*
FROM
(
SELECT
personname
FROM
personnel_filing
WHERE
operationType IN ( 'add', 'del' )
AND operationTime BETWEEN DATE_SUB( NOW( ), INTERVAL 1 MONTH )
AND NOW( )
) AS temp
ORDER BY
operationTime DESC
DATE_SUB() 函数从日期减去指定的时间间隔，本文是从当前时间减去一个月。 CURDATE() 函数表示当前年月日，NOW()函数表示当前年月日时分秒，CURTIME()函数表示时分秒。
扩展
注意最好对修改成between and的形式，如果相减出现负数的时候可能会出问题。
查询昨天的数据
select * from test as 'time' where time.update_time <DATE_SUB(CURDATE(), INTERVAL 1 DAY)
查询当前这周的数据
select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 0 WEEK)
select * FROM test as 'time' where YEARWEEK(date_format(time.update_time,’%Y-%m-%d’)) = YEARWEEK(now())
查询上一周的数据
select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
select * FROM test as 'time' WHERE YEARWEEK(date_format(time.update_time,’%Y-%m-%d’)) = YEARWEEK(now())-1
查询上一个月的数据
select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
查询近两个月的数据（INTERVAL后面的数字改一下就可以了）
select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 2 MONTH)
查询上一年的数据
select * from test as 'time' where time.update_time<DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();
查询今天的数据
select * from test as time where to_days(time.update_time ) =to_days(now());
查询本周的数据
select * FROM test as time WHERE YEARWEEK(date_format(time.update_time ,'%Y-%m-%d')) = YEARWEEK(now());
查询本月的数据
select * FROM test as time WHERE DATE_FORMAT(time.update_time , '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )
查询本季度的数据
select * from test as time where QUARTER(time.update_time)=QUARTER(now());
查询本年的数据
select * from test as time where YEAR(time.update_time)=YEAR(NOW());
查询近一段时间数据（近一周、一个月、半年、一年）
//查询一周
格式：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);
查询指定时间段数据（开始时间-结束时间）–包含结束时间加DATE_ADD()函数：
格式：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)
查询指定时间数据（年、月、日）
//查询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";