Ruby-based date dimension builder using a Kimball-ish pattern.

date_dimension_builder.rb

#!/usr/bin/env ruby
require 'date'
# create table dim_date (
# date_key int primary key not null,
# full_date date,
# unix_timestamp int,
# year int,
# month_number int,
# month_name varchar(255),
# month_name_short varchar(255),
# day_of_month_number int,
# day_of_week_number int,
# day_of_week_name varchar(255),
# week_of_year int,
# year_quarter varchar(255),
# quarter int,
# weekday_weekend varchar(255) );

# holidays would be trivial to add...

begin_date = Date.parse("2000-01-01")
end_date   = Date.parse("2020-12-31")

(begin_date..end_date).each do |date|
  date_data = {:date_key => date.strftime("%Y%m%d").to_i,
    :full_date => date,
    :unix_timestamp => date.to_time.to_i,
    :year => date.year,
    :month_number => date.month,
    :month_name => date.strftime("%B"),
    :month_name_short => date.strftime("%b"),
    :day_of_month_number => date.mday,
    :day_of_week_number => date.cwday,
    :day_of_week_name => date.strftime("%A"),
    :week_of_year => date.cweek,
    :year_quarter => date.year.to_s + "Q" + ((date.month / 4) + 1).to_s,
    :quarter => (date.month / 4) + 1,
    :weekday_weekend => date.cwday >= 6 ? "weekend" : "weekday"}
  date_values = date_data.values.map { |val| val.is_a?(Fixnum) || val.is_a?(Float) ? val : "\"#{val}\"" }
  
  sql  = "insert into dim_date (#{date_data.keys.join(",")}) values "
  sql += "(#{date_values.join(", ")});"
  puts sql
end