select to_char(date,'yyyy-mm-dd'),sum(XXX) from tableXXX where timeXXX between A and B group by to_char(date,'yyyy-mm-dd')
表 ioperationlog 字段 operationtime 例: select count(*) from ioperationlog where (忽略年月日,求每天9点到10点)
select to_char(sysdate,'hh24') from dual;
若operationtime是2011-03-08 15:49:00的字符型 select count(*) from ioperationlog where substr(operationtime,12,2)>'09'and substr(operationtime,12,2)<'10'若operationtime是日期型 select count(*) from ioperationlog where to_char(operationtime,'hh24')>'09'and to_char(operationtime,'hh24')<'10'
有点奇怪的是:select operationtime from ioperationlog where substr(operationtime,13,2)>'09' and substr(operationtime,13,2)<'10' 可以查到 10点 到11点的数据select operationtime from ioperationlog where substr(operationtime,13,2)>'10' and substr(operationtime,13,2)<'11' 就什么也查不到,可是表里明明有许多数据
operationtime 如果是date 类型 这样select count(*) from table where to_char(operation_time,'hh')>='09' and to_char(operation_time,'hh')<'10'
where timeXXX between A and B
group by to_char(date,'yyyy-mm-dd')
字段 operationtime
例:
select count(*) from ioperationlog where (忽略年月日,求每天9点到10点)
select count(*) from ioperationlog where substr(operationtime,12,2)>'09'and substr(operationtime,12,2)<'10'若operationtime是日期型
select count(*) from ioperationlog where to_char(operationtime,'hh24')>'09'and to_char(operationtime,'hh24')<'10'
to_char(sysdate,'hh24') 就是截取24小时制的小时时间段啊
时间字段 operationtime
比如 我要查出 每天9点到10点 用户的登录次数(注意是每天同个时间段)
可以查到 10点 到11点的数据select operationtime from ioperationlog where substr(operationtime,13,2)>'10' and substr(operationtime,13,2)<'11'
就什么也查不到,可是表里明明有许多数据
这样select count(*) from table where to_char(operation_time,'hh')>='09' and to_char(operation_time,'hh')<'10'