oracle sql 按日，周，月，年统计

如:
表:consume_record
字段:consume (money类型)
date (datetime类型)

请问怎么写四条sql语句分别按日,按周,按月,按季统计消费总量.
如:1月 1200元
2月 3400元
3月 2800元

--按日
select sum(consume),day([date]) from consume_record where year([date]) = '2006' group by day([date])

--按周quarter
select sum(consume),datename(week,[date]) from consume_record where year([date]) = '2006' group by datename(week,[date])

--按月
select sum(consume),month([date]) from consume_record where year([date]) = '2006' group by month([date])

--按季
select sum(consume),datename(quarter,[date]) from consume_record where year([date]) = '2006' group by datename(quarter,[date])



--指定日期你就看上面的例子变通下呀，无非就是一个聚合函数和Group by

select [date],sum(consume) from consume_record where [date] between '2006-06-01' and '2006-07-10' group by [date]

以上适用与非ORALE，因为oracle不支持 day函数

oracle中进行上述操作需要

You can choose any of the following options and replace the text string in SQLs to let the reports be as daily/weekly/monthly/Quarterly reports..

--daily
to_char(rad.created_on+15/24, 'YYYY-MM-DD')
--weekly
to_char(next_day(rad.created_on+15/24 - 7,2),'YYYY-MM-DD')

to_char(时间列,'yyyy')||to_char(时间列,'IW')  

--monthly
to_char(rad.created_on+15/24,'yyyy-mm')
--quarterly
to_char(rad.created_on+15/24,'yyyy-q') ||'Q'

 

例如我的sql：

按天----select to_char(to_date(timestamp,'yyyymmddhh24MIss'), 'YYYY-MM-dd') a,sum(num) from udbstat group by to_char(to_date(timestamp,'yyyymmddhh24MIss'), 'YYYY-MM-dd');

按周----select to_char(to_date(timestamp,'yyyymmddhh24MIss'), 'ww') a,sum(num) from udbstat group by to_char(to_date(timestamp,'yyyymmddhh24MIss'), 'ww');

因为我的timestamp是字符串，故需要先转换成date类型。

如果字段本身就是date类型，sql如下：

按小时---select to_char(timestamp, 'YYYY-MM-dd hh24') a,sum(num) from udbstat group by to_char(timestamp,'YYYY-MM-dd hh24');

按天---select to_char(timestamp, 'YYYY-MM-dd') a,sum(num) from udbstat group by to_char(timestamp,'YYYY-MM-dd');

按月---select to_char(timestamp, 'YYYY-MM') a,sum(num) from udbstat group by to_char(timestamp,'YYYY-MM');

按年---select to_char(timestamp, 'YYYY') a,sum(num) from udbstat group by to_char(timestamp,'YYYY');

按季度---select to_char(timestamp, 'YYYY-q') a,sum(num) from udbstat group by to_char(timestamp,'YYYY-q');

按周---select to_char(timestamp, 'ww') a,sum(num) from udbstat group by to_char(timestamp,'ww');