Oracle之时间类型比较

表:BS_T_SM_USER_LOGIN_HISTORY

大于本月的数据

效率低

select * from BS_T_SM_USER_LOGIN_HISTORY where to_date(LONGTODATE(LOGINTIME),'yyyy-mm-dd hh24:mi:ss') > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM'),'YYYY-MM')

效率优化

select * from BS_T_SM_USER_LOGIN_HISTORY where LOGINTIME > datetolong(TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM'),'YYYY-MM'))

之后给LOGINTIME字段加索引

大于本日的数据

select count(LOGINNAME) LNAME from BS_T_SM_USER_LOGIN_HISTORY where to_date(LONGTODATE(LOGINTIME),'yyyy-mm-dd hh24:mi:ss') > TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')

效率优化

select count(LOGINNAME) LNAME from BS_T_SM_USER_LOGIN_HISTORY where LOGINTIME > datetolong(TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))

之后给LOGINTIME字段加索引