Skip to content

Instantly share code, notes, and snippets.

@gotmayonase
Created December 3, 2014 00:06
Show Gist options
  • Save gotmayonase/23fb14bb1a31f600ca53 to your computer and use it in GitHub Desktop.
Save gotmayonase/23fb14bb1a31f600ca53 to your computer and use it in GitHub Desktop.
class CreateCalendarTable < ActiveRecord::Migration
def up
create_table :calendar, id: false do |t|
t.date "_date"
t.string :month_name
t.integer :month_no
t.integer :year
t.integer :day
t.integer :day_of_week
t.integer :day_of_year
t.string :day_name
end
execute %Q{ ALTER TABLE calendar ADD PRIMARY KEY (_date); }
end
def down
drop_table :calendar
end
end
ActiveRecord::Base.connection.execute("DROP PROCEDURE IF EXISTS filldates;")
ActiveRecord::Base.connection.execute <<-SQL
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
WHILE dateStart <= dateEnd DO
INSERT INTO calendar (_date, month_name, month_no, year, day, day_of_week, day_name, day_of_year)
VALUES (dateStart, monthname(dateStart), month(dateStart), year(dateStart), day(dateStart), dayofweek(dateStart), dayname(dateStart), dayofyear(dateStart));
SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
END WHILE;
END;
SQL
ActiveRecord::Base.connection.execute("CALL filldates('2000-01-01','2020-12-31');")
ActiveRecord::Base.connection.execute("DROP PROCEDURE IF EXISTS filldates;")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment