oracle数据库间隔,oracle数据库关于时间间隔的函数

每天按每十五分钟进行间隔的函数：

SELECT to_char(TRUNC(sysdate) + (LEVEL) * 15 / (24 * 60), 'hh24:mi') DATE_JG,

rownum

FROM DUAL

CONNECT BY LEVEL < = 96--96是一天按15分钟间隔一共是96行(级)

每天24小时分组函数：

SELECT TO_CHAR(SYSDATE + ROWNUM / 24, 'HH24') TIMES

FROM DUAL

CONNECT BY ROWNUM <= 24

按照指定的开始时间和结束时间每天是星期几：

SELECT ROWNUM NUM,

TO_CHAR(TO_DATE('2013-09-16', 'YYYY-MM-DD') - ROWNUM, 'DAY') SDATE,--星期几

TO_CHAR(TO_DATE('2013-09-23', 'YYYY-MM-DD') - ROWNUM, 'DD') SDAY--几号

FROM DUAL

CONNECT BY ROWNUM <= 7

order by SDAY; 可以根据几号去分组查询。

任意日期本月的分组函数：

SELECT TO_CHAR(LAST_DAY(TO_DATE(P_TIME, 'YYYY-MM')), 'DD')

INTO V_NUM

FROM DUAL;--先求出这个月一共有多少天，这个月的最后一个日期既是当月的天数

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2013-09', 'YYYY-MM'), +1) - ROWNUM, 'MM-DD') SDAY

FROM DUAL

CONNECT BY ROWNUM <= 31;

每年12个月统计数据：

SELECT B.ROAD_ID,

SUM(DECODE(EXTRACT(MONTH FROM B.SDATE),

1,

B.CAR_NUM,

0)) AS JAN,

SUM(DECODE(EXTRACT(MONTH FROM B.SDATE),

2,

B.CAR_NUM,

0)) AS FEB,

SUM(DECODE(EXTRACT(MONTH FROM B.SDATE),

3,

B.CAR_NUM,

0)) AS MAR,

SUM(DECODE(EXTRACT(MONTH FROM B.SDATE),

4,

B.CAR_NUM,

0)) AS APR,

SUM(DECODE(EXTRACT(MONTH FROM B.SDATE),

5,

B.CAR_NUM,

0)) AS MAY,

SUM(DECODE(EXTRACT(MONTH FROM B.SDATE),

6,

B.CAR_NUM,

0)) AS JUNE,

SUM(DECODE(EXTRACT(MONTH FROM B.SDATE),

7,

B.CAR_NUM,

0)) AS JULY,

SUM(DECODE(EXTRACT(MONTH FROM B.SDATE),

8,

B.CAR_NUM,

0)) AS AUG,

SUM(DECODE(EXTRACT(MONTH FROM B.SDATE),

9,

B.CAR_NUM,

0)) AS SEP,

SUM(DECODE(EXTRACT(MONTH FROM B.SDATE),

10,

B.CAR_NUM,

0)) AS OCT,

SUM(DECODE(EXTRACT(MONTH FROM B.SDATE),

11,

B.CAR_NUM,

0)) AS NOV,

SUM(DECODE(EXTRACT(MONTH FROM B.SDATE),

12,

B.CAR_NUM,

0)) AS DECE

FROM TB_HIS_ROAD60 B