sql 时间匹配的各种条件

通常我们都是通过2个时间段去匹配一个时间列的时候用的都是这种写法

AND  T.START_TIME BETWEEN to_date('2018-06-22 10:20:00', 'yyyy-mm-dd hh24:mi:ss') 
    AND to_date('2018-06-22 10:40:00', 'yyyy-mm-dd hh24:mi:ss') 

此写法会去走索引

或者

AND  T.START_TIME >= to_date('2018-06-22 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
    AND T.START_TIME <= to_date('2018-06-22 11:00:00', 'yyyy-mm-dd hh24:mi:ss')

此写法不会走索引<=不会走的

当2个时间段去匹配2个时间列时可以用这种写法

AND  T.START_TIME >= to_date('2018-06-22 10:00:00', 'yyyy-mm-dd hh24:mi:ss')
    AND T.END_TIME <= to_date('2018-06-22 11:00:00', 'yyyy-mm-dd hh24:mi:ss')

当1一个时间段匹配2个时间列时,or前部分代表start_time和end_time必须同时匹配 2018-06-22 10:20:00才能出现

or后半部分,因为end_time可能会为null(表示无穷大)也要去匹配

((t.START_TIME <= to_date('2018-06-22 10:20:00', 'yyyy-mm-dd hh24:mi:ss') AND
      T.END_TIME >= to_date('2018-06-22 10:20:00', 'yyyy-mm-dd hh24:mi:ss')) OR (
       T.START_TIME <= to_date('2018-06-22 10:20:00', 'yyyy-mm-dd hh24:mi:ss')
       AND T.END_TIME IS NULL))

<choose>   <when test="strategyBeginTime != null and strategyEndTime==null">     and #{strategyBeginTime,jdbcType=TIMESTAMP} &lt;= s.end_time   </when>   <when test="strategyEndTime != null and strategyBeginTime == null">     and #{strategyEndTime,jdbcType=TIMESTAMP} >= s.begin_time   </when>   <when test="strategyEndTime != null and strategyBeginTime != null">     and ((s.begin_time &lt;=#{strategyBeginTime} and s.end_time >#{strategyBeginTime}) or (s.begin_time >=#{strategyBeginTime} and s.begin_time&lt;=#{strategyEndTime}))   </when> </choose>

create table TAB_PAY_COMMISSION_PERCENT
(
 ID NUMBER(11) not null
  constraint PAY_COMMISSION_PERCENT_ID_PK
   primary key,
 PAYMENT_PLATFORM NUMBER(1),
 COMMISSION_PERCENT NUMBER(5,5) default 0,
 DEL_FLAG NUMBER(1) default 0,
 START_TIME DATE default NULL,  
 END_TIME DATE,
 CARD_TYPE NUMBER(2),
 REMARK VARCHAR2(100)
)
/

comment on table TAB_PAY_COMMISSION_PERCENT is '手续费利率表'
/

comment on column TAB_PAY_COMMISSION_PERCENT.ID is '主键'
/

comment on column TAB_PAY_COMMISSION_PERCENT.PAYMENT_PLATFORM is '支付平台'
/

comment on column TAB_PAY_COMMISSION_PERCENT.COMMISSION_PERCENT is '手续费利率'
/

comment on column TAB_PAY_COMMISSION_PERCENT.DEL_FLAG is '删除标记(0:未删除,1:已删除)'
/

comment on column TAB_PAY_COMMISSION_PERCENT.START_TIME is '开始时间'
/

comment on column TAB_PAY_COMMISSION_PERCENT.END_TIME is '结束时间'
/

comment on column TAB_PAY_COMMISSION_PERCENT.CARD_TYPE is '支付卡类型'
/

comment on column TAB_PAY_COMMISSION_PERCENT.REMARK is '备注'
/
INSERT INTO TAB_PAY_COMMISSION_PERCENT (ID, PAYMENT_PLATFORM, COMMISSION_PERCENT, DEL_FLAG, START_TIME, END_TIME, CARD_TYPE, REMARK) VALUES (715602, 1, 0.10000, 0, TO_DATE('2018-06-22 10:51:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2018-06-22 10:59:59', 'YYYY-MM-DD HH24:MI:SS'), 0, null);
INSERT INTO TAB_PAY_COMMISSION_PERCENT (ID, PAYMENT_PLATFORM, COMMISSION_PERCENT, DEL_FLAG, START_TIME, END_TIME, CARD_TYPE, REMARK) VALUES (715603, 1, 0.10000, 0, TO_DATE('2018-06-22 11:00:00', 'YYYY-MM-DD HH24:MI:SS'), null, 0, null);
INSERT INTO TAB_PAY_COMMISSION_PERCENT (ID, PAYMENT_PLATFORM, COMMISSION_PERCENT, DEL_FLAG, START_TIME, END_TIME, CARD_TYPE, REMARK) VALUES (715600, 0, 0.10000, 0, TO_DATE('2018-06-22 10:20:00', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2018-06-22 10:39:59', 'YYYY-MM-DD HH24:MI:SS'), 0, null);
INSERT INTO TAB_PAY_COMMISSION_PERCENT (ID, PAYMENT_PLATFORM, COMMISSION_PERCENT, DEL_FLAG, START_TIME, END_TIME, CARD_TYPE, REMARK) VALUES (715601, 0, 0.01000, 0, TO_DATE('2018-06-22 10:40:00', 'YYYY-MM-DD HH24:MI:SS'), null, 0, null);
INSERT INTO TAB_PAY_COMMISSION_PERCENT (ID, PAYMENT_PLATFORM, COMMISSION_PERCENT, DEL_FLAG, START_TIME, END_TIME, CARD_TYPE, REMARK) VALUES (715597, 2, 0.00000, 0, TO_DATE('2018-06-22 09:51:00', 'YYYY-MM-DD HH24:MI:SS'), null, 0, null);
INSERT INTO TAB_PAY_COMMISSION_PERCENT (ID, PAYMENT_PLATFORM, COMMISSION_PERCENT, DEL_FLAG, START_TIME, END_TIME, CARD_TYPE, REMARK) VALUES (715596, 1, 0.00330, 0, TO_DATE('2018-06-22 09:43:19', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2018-06-22 10:50:59', 'YYYY-MM-DD HH24:MI:SS'), 0, null);
INSERT INTO TAB_PAY_COMMISSION_PERCENT (ID, PAYMENT_PLATFORM, COMMISSION_PERCENT, DEL_FLAG, START_TIME, END_TIME, CARD_TYPE, REMARK) VALUES (715598, 0, 0.10000, 0, TO_DATE('2018-06-22 09:59:59', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2018-06-22 10:19:59', 'YYYY-MM-DD HH24:MI:SS'), 0, null);