Oracle时间统计

2019独角兽企业重金招聘Python工程师标准>>>

select count(*) from Member_user where to_char(CREATEDATE,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd'); --今天 
select count(*) from Member_user where to_char(CREATEDATE,'dd')=to_char(sysdate,'dd'); --当天，所有月份的这个号
select count(*) from Member_user where to_char(CREATEDATE,'mm-dd')=to_char(sysdate,'mm-dd'); 
select count(*) from Member_user where to_char(CREATEDATE,'mm.dd')=to_char(sysdate,'mm.dd'); --当月当天，所有年的这一天
select count(*) from Member_user where to_char(CREATEDATE,'yyyy-iw')=to_char(sysdate,'yyyy-iw');  --本周 
select count(*) from Member_user where to_char(CREATEDATE,'iw')=to_char(sysdate,'iw');  --当周，所有年的这一天
select count(*) from Member_user where to_char(CREATEDATE,'yyyy-mm')=to_char(sysdate,'yyyy-mm'); --本月
select count(*) from Member_user where to_char(CREATEDATE,'mm')=to_char(sysdate,'mm'); --当月
select count(*) from Member_user where to_char(CREATEDATE,'yyyy-q')=to_char(sysdate,'yyyy-q'); --本季度 
select count(*) from Member_user where to_char(CREATEDATE,'q')=to_char(sysdate,'q'); --当季度

SELECT NEXT_DAY (SYSDATE,1) FROM dual; --下个星期日
SELECT NEXT_DAY (SYSDATE,2) FROM dual; --下个星期一
SELECT NEXT_DAY (SYSDATE,7) FROM dual; --下个星期六
SELECT NEXT_DAY (SYSDATE,8) FROM dual; --ORA-01846: 周中的日无效 not a valid day of the week

SELECT NEXT_DAY (SYSDATE,'星期一') FROM dual; --下个星期一
SELECT NEXT_DAY (SYSDATE,'Monday') FROM dual; --ORA-01846: 周中的日无效 not a valid day of the week

--2017-11-17
SELECT TO_CHAR(SYSDATE,'YYYY') FROM dual; -- 2017
SELECT TO_CHAR(SYSDATE,'YYY') FROM dual;  --017
SELECT TO_CHAR(SYSDATE,'YY') FROM dual;  --17
SELECT TO_CHAR(SYSDATE,'Y') FROM dual;  --7
SELECT TO_CHAR(SYSDATE,'MM') FROM dual;  --11
SELECT TO_CHAR(SYSDATE,'M') FROM dual;  --ORA-01821: 日期格式无法识别 date format not recognized
SELECT TO_CHAR(SYSDATE,'RM') FROM dual;  --2017-11-17，XI，即11；2018-04-11，IV，即4。
--罗马数字的符号一共只有7个：I（代表1）、V（代表5）、X（代表10）、L（代表50）、C代表100）、D（代表500）、M（代表1,000）。
SELECT to_char(sysdate,'dd') FROM dual;  -- 2017/11/20  20
SELECT to_char(sysdate,'d') FROM dual;  --星期日~星期六 1~7
SELECT TO_CHAR(SYSDATE,'Q') FROM dual;  -- 第几季度

select last_day(sysdate) from dual;   --该月最后一天
select months_between(to_date('2018.04.11','yyyy.mm.dd'),to_date('2017.11.20','yyyy.mm.dd')) mon_betw from dual; --4.70967741935483870967741935483870967742

Select to_char(Sysdate, 'YYYY-MM-DD')   From Dual;
Select to_char(Sysdate, 'hh24:mi:ss')   From Dual;
select to_char(trunc(sysdate),'YYYY-MM-DD hh24:mi:ss') from dual; --默认截取到日（当日的零点零分零秒）
select to_char(trunc(sysdate,'dd'),'yyyymmdd hh24:mi:ss') from dual; --截取到日（当日的零点零分零秒）

select trunc(sysdate) from dual; --默认截取到日（当日的零点零分零秒）
Select trunc(Sysdate, 'YYYY-MM-DD')   From Dual;  -- ORA-01898: 精度说明符过多 too many precision specifiers

Select trunc(Sysdate, 'MM')   From Dual;  -- 2017-11-21  结果： 2017-11-01
Select trunc(Sysdate, 'month')   From Dual;  -- 2017-11-21  结果： 2017-11-01
Select trunc(Sysdate, 'YYYY')   From Dual;  -- 2017-11-21  结果： 2017-01-01
select trunc(sysdate, 'year') from dual; -- 2017-11-21  结果： 2017-01-01
Select trunc(Sysdate, 'DD')   From Dual;  -- 2017-11-21  结果： 2017-11-21
select trunc(sysdate,'q') from dual; --截取到季度（本季度的第一天）

select trunc(sysdate,'hh24') from dual;  --截取到小时（当前小时，零分零秒）
select trunc(sysdate,'mi') from dual; --截取到分（当前分，零秒）
select trunc(sysdate,'ss') from dual ;--ORA-01899: 精度说明符错误 bad precision specifier
select trunc(sysdate,'ww') from dual;  --本周第2天，即本周一
select trunc(sysdate,'iw') from dual;  --本周第2天，即本周一
select trunc(sysdate,'w') from dual;  --本周第1天，即上周日
select trunc(sysdate-4,'w') from dual;  --(sysdate-n)那一周第1天
select trunc(sysdate,'day') from dual; --本周第1天，即上周日

select  sysdate - interval '7' day  from dual; --当前时间减去7天的时间
select to_date(to_char(sysdate-7,'yyyy/mm/dd'),'yyyy/mm/dd') from dual; 
select to_date(to_char(sysdate-7,'yyyy-mm-dd'),'yyyy-mm-dd') from dual; 

--近一个月 每天登陆用户数目  
select login_type, to_char(logindate,'yyyy-mm-dd'), count(*) from member_user_login_trace 
where logindate > add_months(sysdate, -1)
group by login_type, to_char(logindate,'yyyy-mm-dd');

--这个月的每一天
SELECT TRUNC(SYSDATE, 'MM') + ROWNUM - 1 FROM DUAL CONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'dd'));
--今天以后的30天 包括今天
SELECT SYSDATE + ROWNUM - 1 FROM DUAL CONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'dd'));
SELECT SYSDATE + ROWNUM - 1 FROM DUAL CONNECT BY ROWNUM <= 30;
--2018-04-11 以后的30天 包括今天
with t as
(select rownum-1 rn from dual connect by rownum<=30)
select to_date('2018-04-11','yyyy-mm-dd')+rn from t ;
-- 2015-01-01 到 2015-01-15的每一天
with t as
(select rownum-1 rn from dual connect by rownum<=100)
select to_date('2015-01-01','yyyy-mm-dd')+rn from t 
where to_char(to_date('2015-01-01','yyyy-mm-dd')+rn,'yyyy-mm-dd') between '2015-01-01' and '2015-01-15' ;

select TO_NUMBER(TO_CHAR(LAST_DAY(SYSDATE), 'dd')) FROM DUAL;  --30
select ROWNUM FROM DUAL;  --1
select rownum-1 rn from dual connect by rownum<=30;   -- 0~29

select * from v$version;

 

转载于:https://my.oschina.net/u/2342541/blog/1794019