Oracle日期維度備忘

create or replace view v_timedimension as
select to_date(a.STARTTIME,'yyyy-mm-dd hh24:mi:ss') as starttime ,substr(a.starttime,1,4) as year,
 substr(a.starttime,1,7) as month, substr(a.starttime,1,10) as day,
 --substr(a.starttime,1,13)||':00:00' as hour,
 substr(a.starttime,12,2)||':00:00'||'---'|| substr(a.latertime,12,2)||':00:00' as singlehour,
substr(a.starttime,1,4)||'_'||to_char(to_date(a.STARTTIME,'yyyy-mm-dd hh24:mi:ss'),'iw') as weeknum,to_char(to_date(a.STARTTIME,'yyyy-mm-dd hh24:mi:ss'),'day') as zhouji,
to_char(to_date(a.STARTTIME,'yyyy-mm-dd hh24:mi:ss'),'Q') as quarter
from  (
select  distinct  TO_CHAR(STARTTIME,'YYYY-mm-dd HH24:MI:SS')   as starttime,TO_CHAR(STARTTIME+interval '1' hour,'YYYY-mm-dd HH24:MI:SS') as latertime
from  alhistory  a

 ) a