select * from youtablename where commit_time>=to_char(sysdate,'yyyymmdd')||'0900' and commit_time<=to_char(sysdate,'yyyymmdd')||'1000' select * from youtablename where commit_time>=to_char(sysdate,'yyyymmdd')||'1500' and commit_time<=to_char(sysdate,'yyyymmdd')||'1600'
如果一个SQL实现的话,可以这样select * from youtablename where commit_time>=to_char(sysdate,'yyyymmdd') ¦ ¦'0900' and commit_time <=to_char(sysdate,'yyyymmdd') ¦ ¦'1000' union select * from youtablename where commit_time>=to_char(sysdate,'yyyymmdd') ¦ ¦'1500' and commit_time <=to_char(sysdate,'yyyymmdd') ¦ ¦'1600'
select * from youtablename where commit_time between to_char(sysdate,'yyyymmdd')||'0900' and to_char(sysdate,'yyyymmdd')||'1000' UNION ALL select * from youtablename where commit_time between to_char(sysdate,'yyyymmdd')||'1500' and to_char(sysdate,'yyyymmdd')||'1600'
取今天 9:00 可以这样trunc(sysdate) + to_dsinterval('0 09:00:00')to_dsinterval 函数的作用是固定格式的字符串('day hh:mi:ss')转换成时间段。这个时间段可以和 date 数据类型作 +/- 操作例如select trunc(sysdate) + to_dsinterval('0 09:00:00') from dual;此方法用在楼上 sql 语句的 between 部分就可以了。
select * from A where substr(commit_time, 9) between '090000' and '100000' or substr(commit_time, 9) between '150000' and '160000' 如果不含后面的时间点则应该为: select * from A where substr(commit_time, 9) between '090000' and '095959' or substr(commit_time, 9) between '150000' and '155959'
使用 trunc(commit_time,'ss') 来查询,个人感觉最优化.
字段commit_time应当设定为timestamp类型,这样就方便啦
commit_time 字段如果是字符类型 可以先将转换成日期类型to_date 然后再 to_char转换成字符select to_char(to_date('20080301112536','yyyymmddhhmiss'),'Hh24:mi:ss') from dual下面的代码 可以试试。自己把表名和列名改下。 select a.commit_id, a.commit_name, a.commit_time from (select commit_id, commit_name, commit_time, case when to_char(to_date('20080301112536','yyyymmddhhmiss'),'Hh24:mi')>='09:00' and to_char(to_date('20080301112536','yyyymmddhhmiss'),'Hh24:mi')<='10:00' then 1 when to_char(to_date('20080301112536','yyyymmddhhmiss'),'Hh24:mi')>='09:00' and to_char(to_date('20080301112536','yyyymmddhhmiss'),'Hh24:mi')<='10:00' then 1 else 0 end temp_column from table_name ) a where a.temp_column = 1
select * from youtablename where commit_time>=to_char(sysdate,'yyyymmdd')||'1500' and commit_time<=to_char(sysdate,'yyyymmdd')||'1600'
where commit_time>=to_char(sysdate,'yyyymmdd') ¦ ¦'0900'
and commit_time <=to_char(sysdate,'yyyymmdd') ¦ ¦'1000'
union
select * from youtablename
where commit_time>=to_char(sysdate,'yyyymmdd') ¦ ¦'1500'
and commit_time <=to_char(sysdate,'yyyymmdd') ¦ ¦'1600'
select *
from youtablename
where commit_time between to_char(sysdate,'yyyymmdd')||'0900' and to_char(sysdate,'yyyymmdd')||'1000'
UNION ALL
select *
from youtablename
where commit_time between to_char(sysdate,'yyyymmdd')||'1500' and to_char(sysdate,'yyyymmdd')||'1600'
如果不含后面的时间点则应该为:
select * from A where substr(commit_time, 9) between '090000' and '095959' or substr(commit_time, 9) between '150000' and '155959'
select
a.commit_id,
a.commit_name,
a.commit_time
from
(select commit_id,
commit_name,
commit_time,
case when to_char(to_date('20080301112536','yyyymmddhhmiss'),'Hh24:mi')>='09:00'
and to_char(to_date('20080301112536','yyyymmddhhmiss'),'Hh24:mi')<='10:00' then 1
when to_char(to_date('20080301112536','yyyymmddhhmiss'),'Hh24:mi')>='09:00'
and to_char(to_date('20080301112536','yyyymmddhhmiss'),'Hh24:mi')<='10:00' then 1
else 0
end temp_column
from table_name ) a
where a.temp_column = 1