如果数据库时间字段精确到 秒,则用
- select
- count(*),
- to_char(to_date(create_dt, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24')
- from b2c_order_head
- where create_dt like '2014-02-06%'
- group by to_char(to_date(create_dt, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24')
- order by to_char(to_date(create_dt, 'yyyy-mm-dd hh24:mi:ss'),
- 'yyyy-mm-dd hh24')
如果数据库时间字段精确到 毫秒,则用
select
count(*),
to_char(to_timestamp(l.datetime, 'yyyy-mm-dd hh24:mi:ss.ff')+0, 'yyyy-mm-dd hh24')
from SYS_LOG l
where l.datetime like '2018-03-21%'
group by to_char(to_timestamp(l.datetime, 'yyyy-mm-dd hh24:mi:ss.ff')+0, 'yyyy-mm-dd hh24')
order by to_char(to_timestamp(l.datetime, 'yyyy-mm-dd hh24:mi:ss.ff')+0,
'yyyy-mm-dd hh24')
参考demo
select to_char(to_timestamp('2013/10/10 12:00:01.55', 'yyyy/mm/dd hh24:mi:ss.ff')+0,'hh24:mi' ) from dual
select to_timestamp('2012-5-13 18:45:34:567','yyyy-mm-dd hh24:mi:ss:ff9')+0 from dual;