mysql 查询一段时间内的数据

<!-- 30分钟内 -->

select * from 表名 where  CONFIRM_Time < DATE_SUB(NOW(),INTERVAL 60 MINUTE) 

<!-- 超过30分钟但没超一小时-->
select * from 表名 where CONFIRM_Time < DATE_SUB(NOW(),INTERVAL 30 MINUTE) and CONFIRM_Time > DATE_SUB(NOW(),INTERVAL 60 MINUTE)

<!-- 按日查询 -->  
SELECT DATE_FORMAT(OCCUR_TIME,'%Y-%m-%d') as time,count(event_id) num FROM 表名 where EVENT_STATUS = 4  GROUP BY  time  
<!-- 按月查询 -->  
SELECT DATE_FORMAT(OCCUR_TIME,'%Y-%m') as time,count(event_id) num FROM 表名where EVENT_STATUS = 4  GROUP BY  time  
<!-- 按年查询 -->  
SELECT DATE_FORMAT(OCCUR_TIME,'%Y') as time,count(event_id) num FROM 表名where EVENT_STATUS = 4  GROUP BY  time  
<!-- 按周查询 -->  
SELECT DATE_FORMAT(OCCUR_TIME,'%Y-%u') as time,count(event_id) num FROM 表名where EVENT_STATUS = 4  GROUP BY  time  

<!-- 当天数据 -->

SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())=0

<!-- 昨天数据 -->

SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())=-1

<!-- 之后几天数据 -->

//当n为负数时，表示过去第n天的数据
SELECT * FROM 表名WHERE DATEDIFF(字段,NOW())=n

<!-- n天内所有数据 -->
SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())<n AND DATEDIFF(字段,NOW())>=0

 

<!-- 过去n天内所有数据 -->
SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())<=0 AND DATEDIFF(字段,NOW())>-n
//不包含当天
SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())<0 AND DATEDIFF(字段,NOW())>-n