17,377
社区成员
发帖
与我相关
我的任务
分享
--当前时间为年的第几周 (注意当前年的第一天为周几)
select to_char(sysdate,'ww') from dual;
--当前时间为月的第几周 (注意当前月的第一天为周几)
select to_char(sysdate,'w') from dual;
select to_char(sysdate,'day') from dual;
TO_CHAR(SYSDA
-------------
thursday
-- 那就加个to_date()函数,先将其转换成日期类型:
SELECT BOXID, trunc(to_date(date_column,'yyyy-mm-dd'),'Q') as Qu, sum(energy) as energy FROM boxenergy group by boxid, trunc(to_date(date_column,'yyyy-mm-dd'),'Q');
-- 那就加个to_date()函数,先将其转换成日期类型:
SELECT BOXID, trunc(to_date(date_column,'yyyy-mm-dd),'Q') as Qu, sum(energy) as energy
FROM boxenergy
group by boxid, trunc(to_date(date_column,'yyyy-mm-dd),'Q');
-- 例如:按季度分组:
SELECT BOXID, trunc(date,'Q') as Qu, sum(energy) as energy
FROM boxenergy
group by boxid, trunc(date,'Q');
-- 当前年的第一天:
sys@SZTYORA> select trunc(sysdate,'yyyy') from dual;
TRUNC(SYSDATE,'Q')
-------------------
2011-01-01 00:00:00
-- 当前月的第一天:
select trunc(sysdate,'mm') from dual;
-- 当前周的第一天
sys@SZTYORA> select trunc(sysdate,'DAY') from dual;
TRUNC(SYSDATE,'DAY'
-------------------
2011-02-27 00:00:00