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