Mysql 按当天、当月、上月及按日期范围查询 DATE_FORMAT( date, ‘%Y%m‘ )

//1、根据开始时间结束时间查询
SELECT *
FROM
record
WHERE 1=1
<if test="dateEnd != null and dateEnd != ''"> AND operation_time <![CDATA[ < ]]> str_to_date(#{dateEnd},'%Y-%m-%d %H:%i:%s')</if>
<if test="dateStart != null and dateStart != ''"> AND operation_time <![CDATA[ >= ]]> str_to_date(#{dateStart},'%Y-%m-%d %H:%i:%s')</if>
ORDER BY id DESC
 
//2、查询当天数据
SELECT *
FROM
record
WHERE 1=1
AND DATE_FORMAT( operation_time, '%Y%m%d' ) = DATE_FORMAT( CURDATE() , '%Y%m%d' )
ORDER BY id DESC
 
 
//3、查询当月数据
SELECT *
FROM
record
WHERE 1=1
AND DATE_FORMAT( operation_time, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' )
ORDER BY id DESC

 
//4、查询上个月数据
SELECT *
FROM
record
WHERE 1=1
AND PERIOD_DIFF( DATE_FORMAT( NOW( ) , '%Y%m' ) , DATE_FORMAT( operation_time, '%Y%m' ) ) =1
ORDER BY id DESC


//5.查询当月的第一天的日期
select DATE_ADD(curdate(),interval -day(curdate())+1 day)


//6.查询当月1号到7号之间的数据
select * from record where operation_time <= DATE_ADD(DATE_ADD(curdate(),interval -day(curdate())+1 day)
,INTERVAL 6 DAY) and operation_time>=DATE_ADD(curdate(),interval -day(curdate())+1 day)