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

来源：互联网发布：域名搜索引擎注册编辑：程序博客网时间：2024/05/16 18:15

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

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 DUALCONNECT 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 DUALCONNECT 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