SELECT to_char("日期字段",'YYYY-MM-dd') time ,count(*) FROM "表名" GROUP BY time;
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/3ac24d710ee242e2ab84793582ac24a1.png)
SELECT to_char("日期字段",'YYYY-MM') time ,count(*) FROM "表名" GROUP BY time;
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/72e6de80a46142bc866b6febe88611eb.png)
SELECT to_char("日期字段",'YYYY') time ,count(*) FROM "表名" GROUP BY time;
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/e27f7ee806cc402ab1b891ebbdfa5785.png)
- 统计周几产生多少数据(day:标识星期几的英文全称 比如 :wednesday(周三),dy:标识星期几的简称 比如:wed(周三))
SELECT to_char("日期字段",'day') time ,count(*) FROM "表名" GROUP BY time;
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/68a825b2aace4fde87637be02509bf1a.png)
SELECT
CASE
WHEN to_char("create_time", 'dy') = 'sun' THEN '周日'
WHEN to_char("create_time", 'dy') = 'mon' THEN '周一'
WHEN to_char("create_time", 'dy') = 'tue' THEN '周二'
WHEN to_char("create_time", 'dy') = 'wed' THEN '周三'
WHEN to_char("create_time", 'dy') = 'thu' THEN '周四'
WHEN to_char("create_time", 'dy') = 'fri' THEN '周五'
WHEN to_char("create_time", 'dy') = 'sat' THEN '周六'
END as time ,
count(*)
FROM
"table"
GROUP BY
time
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/11d7803f6d544596afbe9930eb97c4ab.png)
SELECT to_char("日期字段",'Q') time ,count(*),to_char("日期字段",'YYYY') ctime FROM "表名" GROUP BY time,ctime;
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/f2cc4ae34c9e4ddc84d6cd5a61f6a9dd.png)
- 统计每月第几天产生多少数据(和日期统计差不多,3个DDD统计当前年的第多少天)
SELECT to_char("日期字段",'DD') time ,count(*),to_char("日期字段",'YYYY-MM-dd') ctime FROM "表名" GROUP BY time,ctime;
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/38d1966e59384aeaa9607ff3d6ed8ffc.png)
SELECT to_char("日期字段",'YYYY/MM/DD:PM:DY') time ,count(*) FROM "表名" GROUP BY time;
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/d78e5b8731e84b84828bce21015ecdb1.png)
SELECT TO_CHAR("日期字段", 'YYYY/MM/DD') AS NOW,TO_CHAR("日期字段" - INTERVAL '7'day ,'YYYY/MM/DD') AS PAST,count(*) FROM "表名" GROUP BY NOW,PAST;
![在这里插入图片描述](https://i-blog.csdnimg.cn/direct/fea4580eeef44b24bf35b77d8cf31b9f.png)
SELECT TO_CHAR("日期字段", 'YYYY/MM/DD HH24:MI:SS') AS NOW,TO_CHAR("日期字段"- INTERVAL '7' second,'YYYY/MM/DD HH24:MI:SS') AS PAST FROM "表名"
SELECT TO_CHAR("日期字段", 'YYYY/MM/DD HH24:MI:SS') AS NOW,TO_CHAR("日期字段"- INTERVAL '7' minute,'YYYY/MM/DD HH24:MI:SS') AS PAST FROM "表名"
SELECT TO_CHAR("日期字段", 'YYYY/MM/DD HH24:MI:SS') AS NOW,TO_CHAR("日期字段"- INTERVAL '7'hour ,'YYYY/MM/DD HH24:MI:SS') AS PAST FROM "表名"
SELECT TO_CHAR("日期字段", 'YYYY/MM/DD HH24:MI:SS') AS NOW,TO_CHAR("日期字段"- INTERVAL '7'day ,'YYYY/MM/DD HH24:MI:SS') AS PAST FROM "表名"
SELECT TO_CHAR("日期字段", 'YYYY/MM/DD HH24:MI:SS') AS NOW,TO_CHAR("日期字段" - INTERVAL '7'month ,'YYYY/MM/DD HH24:MI:SS') AS PAST FROM "表名"
SELECT TO_CHAR("日期字段", 'YYYY/MM/DD HH24:MI:SS') AS NOW,TO_CHAR("日期字段"- INTERVAL '7'year ,'YYYY/MM/DD HH24:MI:SS') AS PAST FROM "表名"
SELECT TO_CHAR("日期字段", 'YYYY/MM/DD HH24:MI:SS') AS NOW,TO_CHAR("日期字段"- 8*INTERVAL '7'hour ,'YYYY/MM/DD HH24:MI:SS') AS PAST FROM "表名"