按5分钟统计:
select to_char((trunc(sysdate) +
trunc((unix_to_oracle(C700000006) - trunc(sysdate)) * 24 * 60 / 5-1) * 5 / 60 / 24),
'yyyy-mm-dd hh24:mi') || '-' ||
to_char((trunc(sysdate) +
trunc((unix_to_oracle(C700000006) - trunc(sysdate)) * 24 * 60 / 5 ) * 5 / 60 / 24),
'hh24:mi') period,
count(c1) count
from t168
where C700000006 <1309311271
and C700000006>=1309229132
group by to_char((trunc(sysdate) +
trunc((unix_to_oracle(C700000006) - trunc(sysdate)) * 24 * 60 / 5-1) * 5 / 60 / 24),
'yyyy-mm-dd hh24:mi') || '-' ||
to_char((trunc(sysdate) +
trunc((unix_to_oracle(C700000006) - trunc(sysdate)) * 24 * 60 / 5 ) * 5 / 60 / 24),
'hh24:mi')
order by 1;
select to_char((trunc(sysdate) +
trunc((unix_to_oracle(C700000006) - trunc(sysdate)) * 24 * 60 / 5-1) * 5 / 60 / 24),
'yyyy-mm-dd hh24:mi') || '-' ||
to_char((trunc(sysdate) +
trunc((unix_to_oracle(C700000006) - trunc(sysdate)) * 24 * 60 / 5 ) * 5 / 60 / 24),
'hh24:mi'),unix_to_oracle(C700000006) from t168 where C700000006 <1309311271
and C700000006>=1309229132
C700000006 为unix 时间需转化为oracle时间;
按其他分钟数统计 则把5 改成其他时间。