10 oracle日期

在项目上，在做同比环比与日期相关，往往需要用到日期字段。oracle提供的系统日期有sysdate和current_date，我们先来对比它们的区别。
    相同点：都是显示当前系统时间
    不同点：current_date返回的是当前会话时间，而sysdate返回的是服务器时间。
    比如：如果修改当前会话时区，把中国的时区改为东九区，则current_date显示的时间为东九区的时间。东加西减的原则，current_date比sysdate快一个小时。
日期：

--本日
select to_char(current_date,'yyyymmdd') from dual;

--昨日
select to_char(current_date-1,'yyyymmdd') from dual;

--本月
select to_char(current_date,'yyyymm')from dual;

--上月
select to_char(add_months(current_date,-1),'yyyymm') from dual;

--去年
select to_char(current_date,'yyyy')-1 from dual;
select to_char(add_months(current_date,-12),'yyyy') from dual;

--去年同期本日
select to_char(add_months(current_date,-12),'yyyymmdd')from dual;

--去年同期本月
select to_char(add_months(current_date,-12),'yyyymm')from dual;

--日期加8小时
select  to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
        to_char(sysdate + interval '8' hour,'yyyy-mm-dd hh24:mi:ss') 
from dual;

在项目上，数据库日期与系统日期判断 。由于甲方数据库此表录入数据日期是按照伦敦0时区，所以在对比的时候需要加8小时。 

--本日自开客户
cast(bc.create_date::timestamp + '8 hour' as date) = CURRENT_DATE::date 

--本周自开客户
extract(year from bc.create_date::timestamp + '8 hour') = extract(year from now()) 
and extract(week from bc.create_date::timestamp + '8 hour') = extract(week from now())

--上月自开客户
extract(year from bc.create_date::timestamp + '8 hour') = extract(year from now()-interval '1 month') 
and extract(month from bc.create_date::timestamp + '8 hour') = extract(month from now()-interval '1 month') 

--本月自开客户
extract(year from bc.create_date::timestamp + '8 hour') = extract(year from now()) 
and extract(month from bc.create_date::timestamp + '8 hour') = extract(month from now())

--本年自开客户
extract(year from bc.create_date::timestamp + '8 hour') = extract(year from now())

 这个是直接对比

extract(year from cast(t.display_end as date))=(extract(year from CURRENT_DATE))--本年
 

--extract number类型 2019*100=201900               +          05 =201905
(extract(year from cast(t.display_end as date))*100+extract(month from cast(t.display_end as date)))=
(extract(year from CURRENT_DATE)*100+extract(month from CURRENT_DATE)) --本月
 

 
extract(year from cast(t.display_end as date) ) = extract(year from now()) 
and extract(week from cast(t.display_end as date) ) = extract(week from now())
--本周有效拜访
 
cast(t.display_end as date) = CURRENT_DATE --本日