--
-- 日付ディメンションテーブル
--
drop table if exists dates;

create table dates (
id integer primary key auto_increment,
date date,
year integer,
month integer,
day integer,
week integer,
index date(date),
index year(year),
index month(month),
index day(day),
index week(day)
);



<pre class="prettyprint">drop procedure if exists update_dates_table;

delimiter //
create procedure update_dates_table()
begin
declare currentdate date default ‘2008-12-01’;
truncate dates;
while currentdate <= curdate() do
insert into dates(date, year, month, day, week)
values(
currentdate,
year(currentdate),
month(currentdate),
day(currentdate),
week(currentdate, 0)
);
set currentdate = date_add(currentdate, interval 1 day);
end while;
end
//
delimiter ;
</pre>