vertica和oracle,oracle 账号lockOracle和Vertica中构造日历数据

Vertica里面构造日历用法：

SELECT to_number(TO_CHAR(ts::DATE,'yyyymmdd')) as day_id,

year(ts::DATE) as year_of_calendar,

month(ts::DATE) as month_of_year,

dayofweek(ts::DATE) as day_of_week

FROM (

SELECT '01-01-2013'::TIMESTAMP as tm

UNION

SELECT '12-31-2500'::TIMESTAMP as tm

) as t

TIMESERIES ts as '1 Day' OVER (ORDER BY tm);

Oracle里面构造日历用法：

select to_date('20130101', 'yyyymmdd') + (level-1) as day_id,

EXTRACT(YEAR FROM (to_date('20130101', 'yyyymmdd') + (level-1))) as year_of_calendar,

EXTRACT(MONTH FROM (to_date('20130101', 'yyyymmdd') + (level-1))) as month_of_year,

–EXTRACT(DAY FROM (to_date('20130101', 'yyyymmdd') + (level-1)) ) as daynum,

to_char(to_date('20130101', 'yyyymmdd') + (level-1), 'D') as dayofweek

fromoracle账号 dual

connect by level <= to_date('25001231', 'yyyymmdd') –

to_date('20130101', 'yyyymmdd')