Mybatis按年月日时分秒查询，MySQL年月日时分秒查询

Mybatis按时间范围查询，Mybatis按年月日时分秒查询，MySQL按时间范围查询，MySQL年月日时分秒查询 

更多实用源码  www.cx1314.cn

一、时间范围: 1 ==> 年; 2 ==> 月; 3 ==> 周
<select id="getBroadcastStatistics" resultType="com.rd.pojo.vo.BraodcastStatsVo">
        SELECT
            sum(bd.cog_id = '4') AS total,
            sum(bd.proc_state = '1') AS ban,
            sum(bd.proc_state = '2') AS disapper,
            sum(bd.label_state = '0') AS new
            FROM broadcast bd
        <where>
            <if test="timeType == 1">
                DATE_SUB(CURDATE(), INTERVAL 1 YEAR) &lt;= DATE(last_appeartime);
            </if>
            <if test="timeType == 2">
                DATE_SUB(CURDATE(), INTERVAL 1 MONTH ) &lt;= DATE(last_appeartime);
            </if>
            <if test="timeType == 3">
                DATE_SUB(CURDATE(), INTERVAL 1 WEEK ) &lt;= DATE(last_appeartime);
            </if>
        </where>
</select>

二、时间范围: ： 0：今日，1：周，2：月，3：自定义，4：最近七天

更多实用源码  www.cx1314.cn

声明：classType------0：今日，1：周，2：月，3：自定义，4：最近七天
  <choose>
            <when test="classType == 1">
                AND DATE_FORMAT(s.create_time,'%Y%u') = DATE_FORMAT(CURDATE( ),'%Y%u')
            </when>
            
            <when test="classType == 2">
                AND DATE_FORMAT(s.create_time,'%Y%m') = DATE_FORMAT(CURDATE( ),'%Y%m')
            </when>
            
            <when test="classType == 3">
                <choose>
                    <when test="beginTime!=null and beginTime!='' and endTime == '' ">
                        AND Date(s.create_time) between #{beginTime,jdbcType=VARCHAR} and CURDATE()
                    </when>
                    <when test="endTime!=null and endTime!='' and beginTime == '' ">
                        AND Date(s.create_time) <= #{endTime,jdbcType=VARCHAR}
                    </when>
                    <when test="beginTime!=null and beginTime!='' and endTime!=null and endTime!= '' ">
                        AND Date(s.create_time) between #{beginTime,jdbcType=VARCHAR} and #{endTime,jdbcType=VARCHAR}
                    </when>
                    <otherwise>
                        AND Date(s.create_time) = CURDATE()
                    </otherwise>
                </choose>
            </when>
            
            <when test="classType == 4">
                AND date(s.create_time) between date_sub(curdate(), INTERVAL 6 DAY) and curdate()
            </when>
            <otherwise>
                AND Date(s.create_time) = CURDATE()
            </otherwise>
</choose>

三、时间范围: y年，s季，m月 ，天
 /*按照年度查询*/
<if test="params.selected == 'y'.toString() and params.beginTime != null and params.beginTime != '' ">
                AND Year(create_time) &gt;=#{params.beginTime}
 </if>
 /**结束年度查询*/
<if test="params.selected == 'y'.toString() and params.endTime != null and params.endTime != '' ">
                AND Year(create_time) &lt;= #{params.endTime}
</if>

更多实用源码  www.cx1314.cn

/**按照季度查询*/
<if test="params.selected == 's'.toString() and params.beginTime != null and params.beginTime != '' ">
                AND concat(Year(create_time),quarter(create_time)) &gt;= concat(Year(#{params.beginTime}),day(#{params.beginTime}))
</if>
/**结束季度查询*/
<if test="params.selected == 's'.toString() and params.endTime != null and params.endTime != '' ">
                AND concat(Year(create_time),quarter(create_time)) &lt;= concat(Year(#{params.endTime}),day(#{params.endTime}))
</if> 

/**按照月份查询*/
<if test="params.selected == 'm'.toString() and params.beginTime != null and params.beginTime != '' ">
                AND date_format(create_time,'%y%m') &gt;=date_format(#{params.beginTime},'%y%m')
</if>
  /**结束月份查询*/
<if test="params.selected == 'm'.toString() and params.endTime != null and params.endTime != '' ">
                AND date_format(create_time,'%y%m') &lt;= date_format(#{params.endTime},'%y%m')
</if>

<!-- 按照天查询-->
<if test="params.selected == 'd'.toString() and params.beginTime != null and params.beginTime != '' ">
                AND date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
</if>
<!-- 结束天查询-->
<if test="params.selected == 'd'.toString() and params.endTime != null and params.endTime != '' ">
                AND date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
</if>

四、时间范围: y年，s季，m月 ，其他

更多实用源码  www.cx1314.cn

<choose>

           <when test="params.selected ='y'">
                    <if test="params.beginTime != null and params.beginTime != '' ">
                        AND Year(create_time) &gt;=#{params.beginTime}
                    </if>
                    <if test="params.endTime != null and params.endTime != '' ">
                        AND Year(create_time) &lt;= #{params.endTime}
                    </if>
          </when>
          
          <when test="params.selected ='s'">
                    <if test="params.beginTime != null and params.beginTime != '' ">
                        AND concat(Year(create_time),quarter(create_time)) &gt;= concat(Year(#{params.beginTime}),day(#{params.beginTime}))
                    </if>
                    <if test="params.endTime != null and params.endTime != '' ">
                        AND concat(Year(create_time),quarter(create_time)) &lt;= concat(Year(#{params.endTime}),day(#{params.endTime}))
                    </if>
         </when>
         
         <when test="params.selected ='m'">
                    <if test="params.beginTime != null and params.beginTime != '' ">
                        AND date_format(create_time,'%y%m') &gt;=date_format(#{params.beginTime},'%y%m')
                    </if>
                    <if test="params.endTime != null and params.endTime != '' ">
                        AND date_format(create_time,'%y%m') &lt;= date_format(#{params.endTime},'%y%m')
                    </if>
         </when>
         
         <otherwise>
                    <where>
                        <if test="params.beginTime != null and params.beginTime != '' ">
                            AND date_format(create_time,'%y%m%d') &gt;= date_format(#{params.beginTime},'%y%m%d')
                        </if>
                        <if test="params.endTime != null and params.endTime != '' ">
                            AND date_format(create_time,'%y%m%d') &lt;= date_format(#{params.endTime},'%y%m%d')
                        </if>
                    </where>
        </otherwise>
        
</choose>

五、传入时间范围参数类型是字符串
<if test="startTime!=null and startTime.trim() neq ''">
    and date_format(create_time,'%Y-%m-%d %H:%i:%s') &gt;= str_to_date(#{startTime},'%Y-%m-%d %H:%i:%s')
</if>

<if test="endTime!=null and endTime.trim() neq ''">
    and date_format(create_time,'%Y-%m-%d %H:%i:%s') &lt;= str_to_date(#{endTime},'%Y-%m-%d %H:%i:%s')
</if>

或者

 <if test="beginTime != null and beginTime !='' ">
     and  <![CDATA[p.create_time >=DATE_ADD(#{beginTime },interval 1 day)]]>
 </if>
 <if test="endTime != null and endTime !=''">
     and  <![CDATA[p.create_time <=DATE_ADD(#{endTime},interval 1 day)]]>
 </if>

六、传入时间范围参数类型是Date
<if test="startTime!=null and startTime.trim() neq ''">
    and date_format(create_time,'%Y-%m-%d %H:%i:%s') &gt;= date_format(#{startTime},'%Y-%m-%d %H:%i:%s')
</if>
<if test="endTime!=null and endTime.trim() neq ''">
    and date_format(create_time,'%Y-%m-%d %H:%i:%s') &lt;= date_format(#{endTime},'%Y-%m-%d %H:%i:%s')
</if>

七、Mybatis-Plus时间范围查询  

更多实用源码  www.cx1314.cn

Page<Record> page = new Page<>(page, limit);

IPage<Record> result = iRecordService.page(page,
        new LambdaQueryWrapper<Record>()
            .apply(StrUtil.isNotBlank(start_date),
                    "date_format (create_time,'%Y-%m-%d %H:%i:%s') >= date_format('" + start_date + "','%Y-%m-%d %H:%i:%s')")
            .apply(StrUtil.isNotBlank(end_date),
                    "date_format (create_time,'%Y-%m-%d %H:%i:%s') <= date_format('" + end_date + "','%Y-%m-%d %H:%i:%s')")
            .orderByDesc(HmsFaceDetectLog::getOptime));

八、字符串转换成日期 、日期转换成字符串
//字符串转换成日期 
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
   Date date = null; 
   try { 
    date = format.parse(str); 
   } catch (ParseException e) { 
    e.printStackTrace(); 
   } 

更多实用源码  www.cx1314.cn

//日期转换成字符串 
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
   String str = format.format(date);