Oracle和Vertica中构造日历数据

2022-04-02 07:33:58

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

码农公寓

相关文章