Oracle和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
from dual
connect by level <= to_date(‘25001231‘, ‘yyyymmdd‘) -
           to_date(‘20130101‘, ‘yyyymmdd‘)

Oracle和Vertica中构造日历数据,布布扣,bubuko.com

原文：http://blog.csdn.net/jiangshouzhuang/article/details/19122229