<select id="selectDictTypeList" parameterType="SysDictType" resultMap="SysDictTypeResult">
<include refid="selectDictTypeVo"/>
<where>
<if test="dictName != null and dictName != ''">
AND dict_name like concat('%', #{dictName}, '%')
</if>
<if test="status != null and status != ''">
AND status = #{status}
</if>
<if test="dictType != null and dictType != ''">
AND dict_type like concat('%', #{dictType}, '%')
</if>
<if test="params.beginTime != null and params.beginTime != ''"><!-- 开始时间检索 -->
and date_format(create_time,'%y%m%d') >= date_format(#{params.beginTime},'%y%m%d')
</if>
<if test="params.endTime != null and params.endTime != ''"><!-- 结束时间检索 -->
and date_format(create_time,'%y%m%d') <= date_format(#{params.endTime},'%y%m%d')
</if>
</where>
</select>
时间格式化函数date_format
1.判断时间是否是当天
DATE_FORMAT( create_time, '%Y%m%d' ) = DATE_FORMAT( CURDATE( ), '%Y%m%d' )
2.判断是否是本月
DATE_FORMAT( create_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ), '%Y%m' )```
3.判断是否是上个月
```sql
PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( create_time, '%Y%m' ) ) =1
4.判断是否是昨天
queryYesTotal(getDate().get("start"),getDate().get("end")
public Map<String,Date> getDate(){
//查询当前时间前一天的时间
HashMap<String, Date> map = new HashMap<>();
Calendar calendar = Calendar.getInstance();
calendar.set(calendar.get(Calendar.YEAR),calendar.get(Calendar.MONTH),calendar.get(Calendar.DAY_OF_MONTH)-1,0,0,0);
Date start = calendar.getTime();
Calendar calendar1 = Calendar.getInstance();
calendar1.set(calendar1.get(Calendar.YEAR),calendar1.get(Calendar.MONTH),calendar1.get(Calendar.DAY_OF_MONTH)-1,23,59,59);
Date end = calendar1 .getTime();
map.put("start",start);
map.put("end",end);
return map;
}
SELECT
count( 1 )
FROM
edu.edu_wx_rotate_user_open
WHERE
create_time<![CDATA[ >= ]]>#{start} and create_time<![CDATA[ <= ]]>#{end}
AND `type` = '4'