日期和字符转换函数用法（to_date,to_char）select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')as nowTime from dual; //日期转化为字符串 select to_char(sysdate,'yyyy') as nowYear from dual; //获取时间的年 select to_char(sysdate,'mm') as nowMonth from dual; //获取时间的月 select to_char(sysdate,'dd') as nowDay from dual; //获取时间的日 select to_char(sysdate,'hh24') as nowHour from dual; //获取时间的时 select to_char(sysdate,'mi') as nowMinute from dual; //获取时间的分 select to_char(sysdate,'ss') as nowSecond from dual; //获取时间的秒
--创建表 Test
CTEATE TABLE TEST(
ID NUMBER NOT NULL,
MODIFIEDTIME DATE NOT NULL
）
--按天统计
SELECT TO_CHAR(T.MODIFIEDTIME,'YYYY-MM-DD') TIME,COUNT(*) COUNT
FROM TEST T
--这里可加查询条件 WHERE TO_CHAR(T.MODIFIEDTIME,'YYYY') = TO_CHAR(SYSDATE,'YYYY')
GROUP BY TO_CHAR(T.MODIFIEDTIME,'YYYY-MM-DD') --根据日期来分组
ORDER BY TO_CHAR(T.MODIFIEDTIME,'YYYY-MM-DD') ASC NULLS LAST --根据日期排序
--按周统计
SELECT TO_CHAR(T.MODIFIEDTIME,'YYYY') YEAR,TO_CHAR(T.MODIFIEDTIME,'IW') TIME,COUNT(*) COUNT FROM TEST T --这里可加查询条件 WHERE TO_CHAR(T.MODIFIEDTIME,'YYYY') = TO_CHAR(SYSDATE,'YYYY') GROUP BY TO_CHAR(T.MODIFIEDTIME,'IW'),TO_CHAR(T.MODIFIEDTIME,'YYYY') --根据周数来分组 ORDER BY TO_CHAR(T.MODIFIEDTIME,'YYYY'),TO_CHAR(T.MODIFIEDTIME,'IW') ASC NULLS LAST --根据周数来排序
--按月统计
SELECT TO_CHAR(T.MODIFIEDTIME,'YYYY-MM') TIME,COUNT(*) COUNT
FROM TEST T
--这里可加查询条件 WHERE TO_CHAR(T.MODIFIEDTIME,'YYYY') = TO_CHAR(SYSDATE,'YYYY')
GROUP BY TO_CHAR(T.MODIFIEDTIME,'YYYY-MM') --根据月份来分组
ORDER BY TO_CHAR(T.MODIFIEDTIME,'YYYY-MM') ASC NULLS LAST --根据月份来排序
--按季度统计
SELECT TO_CHAR(T.MODIFIEDTIME,'YYYY') YEAR,TO_CHAR(T.MODIFIEDTIME,'Q') TIME,COUNT(*) COUNT FROM TEST T --这里可加查询条件 WHERE TO_CHAR(T.MODIFIEDTIME,'YYYY') = TO_CHAR(SYSDATE,'YYYY') GROUP BY TO_CHAR(T.MODIFIEDTIME,'Q'),TO_CHAR(T.MODIFIEDTIME,'YYYY') --根据每年季度来分组ORDER BY TO_CHAR(T.MODIFIEDTIME,'YYYY'),TO_CHAR(T.MODIFIEDTIME,'Q') ASC NULLS LAST --根据每年季度来排序
--按年统计
SELECT TO_CHAR(T.MODIFIEDTIME,'YYYY') YEAR,COUNT(*) COUNT FROM TEST T --这里可加查询条件 GROUP BY TO_CHAR(T.MODIFIEDTIME,'YYYY') --根据年份来分组ORDER BY TO_CHAR(T.MODIFIEDTIME,'YYYY') ASC NULLS LAST --根据年份来排序