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