I'm trying to create a date table in redshift with the following columns:
note that year month is in the format : '202001' and month name year is in the format: january2020 . how do I insert data in the table after I create it?
appreciate the help..
This is fairly straight forward in Redshift. A recursive CTE can be use to make the list of dates and some simple formatting can be used to get the fields you desire. This example makes all the dates from Jan 1, 2020 until today.
with recursive dates(dt) as
( select '2020-01-01'::date as dt
union all
select dt + 1
from dates d
where d.dt <= current_date
)
select dt, extract(year from dt) as year, extract(month from dt) as month,
decode(month, 1, 'January', 2, 'February', 3, 'March', 4, 'April', 5, 'May', 6, 'June', 7, 'July', 8, 'August', 9, 'September', 10, 'October', 11, 'November', 12, 'December') as mname,
year * 100 + month as yearmo, mname || year::text as monameyr
from dates order by dt;
Frankly, the easiest way to create a Calendar table is to make it in Excel!
Make a row for each date, the use formulas to add columns for day, month, year, day number, etc. You can also add booleans to identify weekends and public holidays. You could add a column for the first and last days of the month (eg to lookup the last day of a month after a given date).
Then, save the data as a CSV file and import it into the Redshift table.
Yes, you could write some fancy SQL using the generate_series() function that makes a table purely within Redshift, but it can be a little painful to write and doing it in Excel gives you a chance to add other functionality (such as public holidays).