Em5kV5Zn

1set hivevar:start_date=0000-01-01;
2set hivevar:days=1000000;
3set hivevar:table_name=tb_date;
4
5-- If you are running a version of HIVE prior to 1.2, comment out all uses of date_format() and uncomment the lines below for equivalent functionality
6
7CREATE TABLE IF NOT EXISTS ${table_name} AS
8WITH dates AS (
9    SELECT date_add("${start_date}", a.pos) as `date`
10    FROM (SELECT posexplode(split(repeat(",", ${days}), ","))) a
11),
12dates_expanded AS (
13  SELECT
14    `date`,
15    year(`date`) as year,
16    month(`date`) as month,
17    day(`date`) as day,
18    date_format(`date`, 'u') as day_of_week 
19    -- from_unixtime(unix_timestamp(date, "yyyy-MM-dd"), "u") as day_of_week
20  FROM dates
21)
22SELECT
23    `date`,
24    year,
25    cast(month(`date`)/4 + 1 AS BIGINT) as quarter,
26    month,
27    date_format(`date`, 'W') as week_of_month, 
28    -- from_unixtime(unix_timestamp(date, "yyyy-MM-dd"), "W") as week_of_month,
29    date_format(`date`, 'w') as week_of_year, 
30    -- from_unixtime(unix_timestamp(date, "yyyy-MM-dd"), "w") as week_of_year,
31    day,
32    day_of_week,
33    date_format(`date`, 'EEE') as day_of_week_s, 
34    -- from_unixtime(unix_timestamp(date, "yyyy-MM-dd"), "EEE") as day_of_week_s,
35    date_format(`date`, 'D') as day_of_year, 
36    -- from_unixtime(unix_timestamp(date, "yyyy-MM-dd"), "D") as day_of_year,
37    datediff(`date`, "1970-01-01") as day_of_epoch,
38    if(day_of_week BETWEEN 6 AND 7, true, false) as weekend,
39    if(
40      ((month = 1 AND day = 1 AND day_of_week between 1 AND 5) OR (day_of_week = 1 AND month = 1 AND day BETWEEN 1 AND 3)) -- New Year's Day
41      OR (month = 3 AND day = 30) -- National Holiday - Good Friday
42      OR (month = 7 AND day BETWEEN 1 AND 2) -- Canada Day
43      OR (month = 9 AND day = 3) -- Labor Day
44      OR (month = 10 AND day = 8) -- Thanksgiving
45      OR ((month = 12 AND day = 25 AND day_of_week between 1 AND 5) OR (day_of_week = 1 AND month = 12 AND day BETWEEN 25 AND 27)) -- Christmas
46     ,true, false) as can_holiday
47FROM dates_expanded
48SORT BY `date`
49;