Mybatis精确查找时间段范围内数据

在SQL中，经常有查询某个create_date大于orderCreateTimeStr，小于orderCreateTimeEndStr的业务。可用下面这种方法，格式化类别可以为：yyyy-MM-dd E HH:mm:ss   或者：yyyy-MM-dd 等等

1、格式：yyyy-MM-dd

• 订单创建开始时间：orderCreateTimeStr
• 订单创建截止时间：orderCreateTimeEndStr

表达式：

 <if test="orderCreateTimeStr != null and orderCreateTimeStr != ''">
                AND DATE_FORMAT(a.create_date,'%Y-%m-%d') &gt;=  #{orderCreateTimeStr}
  </if>
  <if test="orderCreateTimeEndStr != null and orderCreateTimeEndStr != ''">
                AND DATE_FORMAT(a.create_date,'%Y-%m-%d') &lt;= #{orderCreateTimeEndStr}
  </if>

SQL案例：

	<select id="findListForJsonList" resultType="MainOrder">
		SELECT 
			<include refid="mainOrderColumns"/> ,(case when a.order_type in ('1','2','0','6') then ru.name
			when a.order_type='5' then b.station_waiter_name end ) AS "receiveUser.name"
		FROM t_main_order a
		left join t_recycler_shipment_order b on a.order_no = b.order_no and b.del_flag = '0'		
		<include refid="mainOrderJoins"/>
		<where>
			a.del_flag = #{DEL_FLAG_NORMAL}
			<!-- <if test="orderNo != null and orderNo != ''">
				AND a.order_no like concat('%',#{orderNo},'%') 
			</if> -->
			<if test="orderNos!=null and orderNos!=''">
		      and a.order_no in
		      <foreach item="orderNos" index="index" collection="orderNos" open="(" separator="," close=")">  
              #{orderNos}  
              </foreach> 
		    </if>
		    <if test="createUserNames!=null and createUserNames!=''">
		      and cu.name in
		      <foreach item="createUserNames" index="index" collection="createUserNames" open="(" separator="," close=")">  
              #{createUserNames}  
              </foreach> 
		    </if>
		    <if test="receiveUserNames!=null and receiveUserNames!=''">
		      and (case when a.order_type in ('1','2','0','6') then ru.name
					when a.order_type='5' then b.station_waiter_name end ) AS "receiveUser.name" in 
		      <foreach item="receiveUserNames" index="index" collection="receiveUserNames" open="(" separator="," close=")">  
              #{receiveUserNames}  
              </foreach> 
		    </if>
			<if test="orderType != null and orderType != ''">
				AND a.order_type = #{orderType}
			</if>
			<if test="orderStatus != null and orderStatus != ''">
				AND a.order_status = #{orderStatus}
			</if>
			<!-- <if test="orderCreateUser != null and orderCreateUser != ''">
				AND a.order_create_user = #{orderCreateUser}
			</if> -->
			<if test="orderCreateTime != null and orderCreateTime != ''">
				AND a.order_create_time = #{orderCreateTime}
			</if>
			<if test="payMethod != null and payMethod != ''">
				AND a.pay_method = #{payMethod}
			</if>
			<!-- <if test="orderReceiveUser != null and orderReceiveUser != ''">
				AND a.order_receive_user = #{orderReceiveUser}
			</if> -->
			<if test="orderReceiveTime != null and orderReceiveTime != ''">
				AND a.order_receive_time = #{orderReceiveTime}
			</if>
			<if test="orderCannelTime != null and orderCannelTime != ''">
				AND a.order_cannel_time = #{orderCannelTime}
			</if>
			<if test="orderComplaintsTime != null and orderComplaintsTime != ''">
				AND a.order_complaints_time = #{orderComplaintsTime}
			</if>
			<if test="orderFinishTimeStr != null and orderFinishTimeStr != ''">
				AND DATE_FORMAT(a.order_finish_time,'%Y-%m-%d') &gt;=  #{orderFinishTimeStr}
			</if>
			<if test="orderFinishTimeEndStr != null and orderFinishTimeEndStr != ''">
				AND DATE_FORMAT(a.order_finish_time,'%Y-%m-%d') &lt;= #{orderFinishTimeEndStr}
			</if>
			<if test="orderCreateTimeStr != null and orderCreateTimeStr != ''">
				AND DATE_FORMAT(a.create_date,'%Y-%m-%d') &gt;=  #{orderCreateTimeStr}
			</if>
			<if test="orderCreateTimeEndStr != null and orderCreateTimeEndStr != ''">
				AND DATE_FORMAT(a.create_date,'%Y-%m-%d') &lt;= #{orderCreateTimeEndStr}
			</if>
			<if test="orderReceiveTimeStr != null and orderReceiveTimeStr != ''">
				AND DATE_FORMAT(a.order_receive_time,'%Y-%m-%d') &gt;=  #{orderReceiveTimeStr}
			</if>
			<if test="orderReceiveTimeEndStr != null and orderReceiveTimeEndStr != ''">
				AND DATE_FORMAT(a.order_receive_time,'%Y-%m-%d') &lt;= #{orderReceiveTimeEndStr}
			</if>
			<!-- <if test="createUser.name != null and createUser.name != ''">
				AND cu.name like concat('%',#{createUser.name},'%') 
			</if> -->
			<!-- <if test="receiveUser.name != null and receiveUser.name != ''">
				AND ru.name like concat('%',#{receiveUser.name},'%') 
			</if> -->
			<if test="communityCode != null and communityCode != ''">
                AND a.community_code = #{communityCode}
            </if>
			<!-- <if test="receiveUser.name != null and receiveUser.name != ''">
				AND ru.name concat('%',#{receiveUser.name},'%') 
			</if> -->
		<if test="orderTypes!=null and orderTypes!=''">
		  and a.order_type in
		<foreach item="orderTypes" index="index" collection="orderTypes" open="(" separator="," close=")">  
         #{orderTypes}  
        </foreach> 
		</if>
		
		<if test="orderStatuses!=null and orderStatuses!=''" >
		  and a.order_status in
		<foreach item="orderStatuses" index="index" collection="orderStatuses" open="(" separator="," close=")">  
         #{orderStatuses}  
        </foreach> 
		</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
				ORDER BY a.update_date DESC
			</otherwise>
		</choose>
	</select>
	

2、格式：yyyy-MM-dd E HH:mm:ss

 <select id="selectValidChanceByCondition" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List" />
        from tb_activity_lottery_chance
        <where>
            <if test="activityId != null">
                and activity_id = #{activityId,jdbcType=BIGINT}
            </if>
            <if test="userId != null">
                and user_id = #{userId,jdbcType=BIGINT}
            </if>
            <if test="status != null">
                and status = #{status,jdbcType=INTEGER}
            </if>
            <if test="validStartTime != null">
                and <![CDATA[unix_timestamp(valid_start_time) > unix_timestamp(#{validStartTime})]]>
            </if>
            <if test="validEndTime != null">
                and <![CDATA[unix_timestamp(valid_end_time) < unix_timestamp(#{validEndTime})]]>
            </if>
            and isactive = 1
        </where>
 </select>