Mybatis-plus Mapper配置文件 Oracle和postgres 时间比较查询差异

    Date sysDate = new Date();  //系统当前时间
    String reportDatestr =  DateUtils.date2String(sysDate, Constants.YYYYMMDD);

    if ("0".equals(timeFlag)){
        // 根据日期比较进行查询 获取最近三个月的时间
        criteria.andGreaterThan("reportDate", DateUtils.addMonth(reportDateSys , -2) );
    }else if ("1".equals(timeFlag)){
        criteria.andGreaterThan("reportDate", DateUtils.addMonth(reportDateSys , -4) );
    }
    
        tbMiddle.setDateStartStr(reportDatestr);
        tbMiddle.setDateEndtStr(DateUtils.date2String(DateUtils.addDay(sysDate , 10)));

Oracle Mybatis Mapper.xml 时间查询：

  <!-- 根据模糊条件查询记录 -->
  <select id="getTbReportKindMiddleCountResult" parameterType="com.elianda.capitalclearing.pojo.TbReportKindMiddle"
          resultType="java.util.HashMap" flushCache="true" >
    SELECT ORG_CODE as orgCode , REPORT_KIND_ID as reportKindId, COUNT(REPORT_KIND_ID)  as countResult
    FROM TB_REPORT_KIND_MIDDLE
    <where>
      <if test="orgCode!=null and orgCode!=''"> ORG_CODE =#{orgCode}</if>
      <!-- 提交时间start -->
      <if test="reportItemDateStartStr !=null">
        　AND CREATE_TIME &gt;= to_date(#{reportItemDateStartStr},'yyyy-MM-dd hh24:mi:ss')
      </if>
      <!-- 提交时间end -->
      <if test="reportItemDateEndtStr !=null">
          AND CREATE_TIME &lt;= to_date(#{reportItemDateEndtStr},'yyyy-MM-dd hh24:mi:ss')
      </if>
      GROUP BY ORG_CODE , REPORT_KIND_ID
    </where>
  </select>

postgres时间查询：

    <select id="XXX" parameterType="String" resultType="java.util.HashMap">
       select current_status_code as currentStatusCode, count(1) as countResult from XXX
       where date_format(date,'%Y-%m')=date_format(now(),'%Y-%m')
       group by current_status_code ;
    </select>

    <select id="XXX" parameterType="String" resultType="java.util.HashMap">
       select current_status as currentStatus, count(1) as countResult from XXX
       where date_format(date,'%Y-%m')=date_format(now(),'%Y-%m')
       group by current_status ;
    </select>

查询工具里面的查询日期转换：

SELECT ORG_CODE as orgCode , REPORT_KIND_ID as reportKindId, COUNT(REPORT_KIND_ID) as countResult
FROM TB_REPORT_KIND_MIDDLE WHERE ORG_CODE >= ‘03164’
and date_format(date,’%Y-%m-%d’) >= date_format( ,’%Y-%m-d’)
and date_format(date,’%Y-%m-%d’) <= date_format(new ,’%Y-%m-d’))
GROUP BY ORG_CODE , REPORT_KIND_ID

SELECT ORG_CODE as orgCode , REPORT_KIND_ID as reportKindId, COUNT(REPORT_KIND_ID) as countResult FROM TB_REPORT_KIND_MIDDLE
WHERE CREATE_TIME >= to_date(‘2020-08-01’,‘yyyy-MM-dd hh24:mi:ss’)
AND CREATE_TIME <= to_date(‘2020-10-20’,‘yyyy-MM-dd hh24:mi:ss’)
GROUP BY ORG_CODE , REPORT_KIND_ID