Created
January 11, 2012 04:01
-
-
Save andrewrcollins/1592943 to your computer and use it in GitHub Desktop.
MySQL Dates and Times
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- =================================================================== | |
| -- Various MySQL stored procedures for handling dates, | |
| -- as well as populating date and time dimensions. | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- Date Dimension | |
| -- | |
| CREATE TABLE dates | |
| ( | |
| id INT(11) NOT NULL, | |
| date DATE NOT NULL, | |
| day INT(11) NOT NULL, | |
| day_of_week INT(11) NOT NULL, | |
| day_name VARCHAR(10) NOT NULL, | |
| is_weekday TINYINT(1) NOT NULL, | |
| is_weekend TINYINT(1) NOT NULL, | |
| is_business_day TINYINT(1) NOT NULL, | |
| is_bank_holiday TINYINT(1) NOT NULL, | |
| is_public_holiday TINYINT(1) NOT NULL, | |
| month INT(11) NOT NULL, | |
| month_name VARCHAR(10) NOT NULL, | |
| yearweek VARCHAR(6) NOT NULL, | |
| yearweek_year INT(11) NOT NULL, | |
| yearweek_week INT(11) NOT NULL, | |
| quarteryear VARCHAR(6) NOT NULL, | |
| quarter INT(11) NOT NULL, | |
| year INT(11) NOT NULL, | |
| PRIMARY KEY (id) | |
| ); | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- Time Dimension | |
| -- | |
| CREATE TABLE times | |
| ( | |
| id INT(11) NOT NULL, | |
| time_hms TIME NOT NULL, | |
| time_hm_id INT(11) NOT NULL, | |
| time_hm TIME NOT NULL, | |
| time_h_id INT(11) NOT NULL, | |
| time_h TIME NOT NULL, | |
| hour INT(11) NOT NULL, | |
| minute INT(11) NOT NULL, | |
| second INT(11) NOT NULL, | |
| hour_12 INT(11) NOT NULL, | |
| is_am TINYINT(1) NOT NULL, | |
| is_pm TINYINT(1) NOT NULL, | |
| is_business TINYINT(1) NOT NULL, | |
| format_hms VARCHAR(8) NOT NULL, | |
| format_hm VARCHAR(8) NOT NULL, | |
| format_h VARCHAR(8) NOT NULL, | |
| format_hms_12 VARCHAR(11) NOT NULL, | |
| format_hm_12 VARCHAR(11) NOT NULL, | |
| format_h_12 VARCHAR(11) NOT NULL, | |
| PRIMARY KEY (id) | |
| ); | |
| -- =================================================================== | |
| -- | |
| -- is_saturday() returns whether date is a Saturday | |
| -- | |
| DROP FUNCTION IF EXISTS is_saturday$$ | |
| CREATE FUNCTION is_saturday | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- weekday index (0 = Monday, 1 = Tuesday, ..., 6 = Sunday) | |
| IF(WEEKDAY(the_date) = 5) THEN | |
| RETURN(1); | |
| ELSE | |
| RETURN(0); | |
| END IF; | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_sunday() returns whether date is a Sunday | |
| -- | |
| DROP FUNCTION IF EXISTS is_sunday$$ | |
| CREATE FUNCTION is_sunday | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- weekday index (0 = Monday, 1 = Tuesday, ..., 6 = Sunday) | |
| IF(WEEKDAY(the_date) = 6) THEN | |
| RETURN(1); | |
| ELSE | |
| RETURN(0); | |
| END IF; | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_weekday() returns whether date is not Saturday or Sunday | |
| -- | |
| DROP FUNCTION IF EXISTS is_weekday$$ | |
| CREATE FUNCTION is_weekday | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- check if date date is a Saturday | |
| IF(is_saturday(the_date) = 1) THEN | |
| RETURN(0); | |
| END IF; | |
| -- check if date date is a Sunday | |
| IF(is_sunday(the_date) = 1) THEN | |
| RETURN(0); | |
| END IF; | |
| RETURN(1); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_weekend() returns whether date is a Saturday or Sunday | |
| -- | |
| DROP FUNCTION IF EXISTS is_weekend$$ | |
| CREATE FUNCTION is_weekend | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- check if date date is a Saturday | |
| IF(is_saturday(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| -- check if date date is a Sunday | |
| IF(is_sunday(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- get_nth_weekday_of_month() returns nth weekeday of the month | |
| -- | |
| DROP FUNCTION IF EXISTS get_nth_weekday_of_month$$ | |
| CREATE FUNCTION get_nth_weekday_of_month | |
| ( | |
| nth INT(11), | |
| weekday_index INT(11), | |
| month INT(11), | |
| year INT(11) | |
| ) | |
| RETURNS DATE | |
| DETERMINISTIC | |
| BEGIN | |
| DECLARE the_date DATE; | |
| -- construct first day of month | |
| SET the_date = CONCAT(year, "-", month, "-1"); | |
| IF(nth >= 0) THEN | |
| -- identify first specified weekday of month | |
| WHILE (WEEKDAY(the_date) <> weekday_index) DO | |
| -- add one day | |
| SET the_date = DATE_ADD(the_date, INTERVAL 1 DAY); | |
| END WHILE; | |
| SET nth = nth - 1; | |
| WHILE nth > 0 DO | |
| -- add one week | |
| SET the_date = DATE_ADD(the_date, INTERVAL 1 WEEK); | |
| -- decrement nth | |
| SET nth = nth - 1; | |
| END WHILE; | |
| ELSE | |
| -- get last day of month | |
| SET the_date = LAST_DAY(the_date); | |
| -- identify first specified weekday of month | |
| WHILE (WEEKDAY(the_date) <> weekday_index) DO | |
| -- subtract one day | |
| SET the_date = DATE_SUB(the_date, INTERVAL 1 DAY); | |
| END WHILE; | |
| SET nth = nth + 1; | |
| WHILE nth < 0 DO | |
| -- subtract one week | |
| SET the_date = DATE_SUB(the_date, INTERVAL 1 WEEK); | |
| -- increment nth | |
| SET nth = nth + 1; | |
| END WHILE; | |
| END IF; | |
| RETURN(the_date); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- easter_day() returns Easter day for specified year | |
| -- | |
| DROP FUNCTION IF EXISTS easter_day$$ | |
| CREATE FUNCTION easter_day | |
| ( | |
| the_year INT | |
| ) | |
| RETURNS DATE | |
| DETERMINISTIC | |
| BEGIN | |
| -- intermediate variables | |
| DECLARE k INT; | |
| DECLARE a INT; | |
| DECLARE b INT; | |
| DECLARE c INT; | |
| DECLARE q INT; | |
| DECLARE p INT; | |
| DECLARE m INT; | |
| DECLARE d INT; | |
| DECLARE n INT; | |
| DECLARE e INT; | |
| -- Easter day | |
| DECLARE easter DATE; | |
| -- messy calculations | |
| SET k = FLOOR(the_year / 100); | |
| SET a = MOD(the_year, 19); | |
| SET b = MOD(the_year, 4); | |
| SET c = MOD(the_year, 7); | |
| SET q = FLOOR(k / 4); | |
| SET p = FLOOR((13 + 8 * k) / 25); | |
| SET m = MOD((15 - p + k - q), 30); | |
| SET d = MOD((19 * a + m), 30); | |
| SET n = MOD((4 + k - q), 7); | |
| SET e = MOD((2 * b + 4 * c + 6 * d + n), 7); | |
| -- calculate Easter day | |
| SET easter = CASE | |
| WHEN d + e <= 9 THEN | |
| CONCAT_WS('-', the_year, '03', 22 + d + e) | |
| WHEN d = 29 AND e = 6 THEN | |
| CONCAT_WS('-', the_year, '04-19') | |
| WHEN d = 28 AND e = 6 AND a > 10 THEN | |
| CONCAT_WS('-', the_year, '04-18') | |
| ELSE | |
| CONCAT_WS('-', the_year, '04', LPAD(d + e - 9, 2, 0)) | |
| END; | |
| -- return Easter day | |
| RETURN easter; | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_groundhog_day() returns whether date is Groundhog Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_groundhog_day$$ | |
| CREATE FUNCTION is_groundhog_day | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Groundhog Day, February 2nd | |
| SET the_holiday = CONCAT(the_year, "-2-2"); | |
| -- check for match | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_valentines_day() returns whether date is Valentine's Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_valentines_day$$ | |
| CREATE FUNCTION is_valentines_day | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Valentine's Day, February 14th | |
| SET the_holiday = CONCAT(the_year, "-2-14"); | |
| -- check for match | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_st_patricks_day() returns whether date is St. Patrick's Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_st_patricks_day$$ | |
| CREATE FUNCTION is_st_patricks_day | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- St. Patrick's Day, March 17th | |
| SET the_holiday = CONCAT(the_year, "-3-17"); | |
| -- check for match | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_easter() returns whether date is Easter | |
| -- | |
| DROP FUNCTION IF EXISTS is_easter$$ | |
| CREATE FUNCTION is_easter | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- ##### | |
| -- get Easter day using year of the date | |
| SET the_holiday = easter_day(YEAR(the_date)); | |
| -- check for match | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_april_fools_day() returns whether date is April Fools's Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_april_fools_day$$ | |
| CREATE FUNCTION is_april_fools_day | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- April Fools's Day, April 1st | |
| SET the_holiday = CONCAT(the_year, "-4-1"); | |
| -- check for match | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_mothers_day() returns whether date is Mothers' Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_mothers_day$$ | |
| CREATE FUNCTION is_mothers_day | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Mothers' Day, 2nd Sunday in May | |
| SET the_holiday = get_nth_weekday_of_month(2, 6, 5, the_year); | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_fathers_day() returns whether date is Fathers' Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_fathers_day$$ | |
| CREATE FUNCTION is_fathers_day | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Fathers' Day, 3rd Sunday in June | |
| SET the_holiday = get_nth_weekday_of_month(3, 6, 6, the_year); | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_parents_day() returns whether date is Parents' Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_parents_day$$ | |
| CREATE FUNCTION is_parents_day | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Parents' Day, 4th Sunday in July | |
| SET the_holiday = get_nth_weekday_of_month(4, 6, 7, the_year); | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_grandparents_day() returns whether date is Grandparents' Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_grandparents_day$$ | |
| CREATE FUNCTION is_grandparents_day | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Labor Day, 1st Monday in September | |
| SET the_holiday = get_nth_weekday_of_month(1, 0, 9, the_year); | |
| -- Grandparents' Day, Sunday after Labor Day | |
| WHILE (WEEKDAY(the_holiday) <> 6) DO | |
| -- add one day | |
| SET the_holiday = DATE_ADD(the_holiday, INTERVAL 1 DAY); | |
| END WHILE; | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_halloween() returns whether date is Halloween | |
| -- | |
| DROP FUNCTION IF EXISTS is_halloween$$ | |
| CREATE FUNCTION is_halloween | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Halloween, October 31st | |
| SET the_holiday = CONCAT(the_year, "-10-31"); | |
| -- check for match | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_new_years_day() returns whether date is New Year's Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_new_years_day$$ | |
| CREATE FUNCTION is_new_years_day | |
| ( | |
| the_date DATE, | |
| bankers_time TINYINT(1) | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- New Year's Day, January 1 (bankers time; following Monday, if Sunday) | |
| SET the_holiday = CONCAT(the_year, "-1-1"); | |
| IF((bankers_time = 1) && (WEEKDAY(the_holiday) = 6)) THEN | |
| SET the_holiday = DATE_ADD(the_holiday, INTERVAL 1 DAY); | |
| END IF; | |
| -- check for match | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_mlkjr_day() returns whether the date is Birthday of Martin Luther King, Jr. | |
| -- | |
| DROP FUNCTION IF EXISTS is_mlkjr_day$$ | |
| CREATE FUNCTION is_mlkjr_day | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Birthday of Martin Luther King, Jr., 3rd Monday in January | |
| SET the_holiday = get_nth_weekday_of_month(3, 0, 1, the_year); | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_washington_day() returns whether the date is Washington's Birthday | |
| -- | |
| DROP FUNCTION IF EXISTS is_washington_day$$ | |
| CREATE FUNCTION is_washington_day | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Washington's Birthday, 3rd Monday in February | |
| SET the_holiday = get_nth_weekday_of_month(3, 0, 2, the_year); | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_memorial_day() returns whether the date is Memorial Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_memorial_day$$ | |
| CREATE FUNCTION is_memorial_day | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Memorial Day, last Monday in May | |
| SET the_holiday = get_nth_weekday_of_month(-1, 0, 5, the_year); | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_independence_day() returns whether the date is Independence Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_independence_day$$ | |
| CREATE FUNCTION is_independence_day | |
| ( | |
| the_date DATE, | |
| bankers_time TINYINT(1) | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Independence Day, July 4 (bankers time; following Monday, if Sunday) | |
| SET the_holiday = CONCAT(the_year, "-7-4"); | |
| IF((bankers_time = 1) && (WEEKDAY(the_holiday) = 6)) THEN | |
| SET the_holiday = DATE_ADD(the_holiday, INTERVAL 1 DAY); | |
| END IF; | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_labor_day() returns whether the date is Labor Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_labor_day$$ | |
| CREATE FUNCTION is_labor_day | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Labor Day, 1st Monday in September | |
| SET the_holiday = get_nth_weekday_of_month(1, 0, 9, the_year); | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_columbus_day() returns whether the date is Columbus Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_columbus_day$$ | |
| CREATE FUNCTION is_columbus_day | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Columbus Day, 2nd Monday in October | |
| SET the_holiday = get_nth_weekday_of_month(2, 0, 10, the_year); | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_veterans_day() returns whether the date is Veterans Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_veterans_day$$ | |
| CREATE FUNCTION is_veterans_day | |
| ( | |
| the_date DATE, | |
| bankers_time TINYINT(1) | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Veterans Day, November 11 (bankers time; following Monday, if Sunday) | |
| SET the_holiday = CONCAT(the_year, "-11-11"); | |
| IF((bankers_time = 1) && (WEEKDAY(the_holiday) = 6)) THEN | |
| SET the_holiday = DATE_ADD(the_holiday, INTERVAL 1 DAY); | |
| END IF; | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_thanksgiving_day() returns whether the date is Thanksgiving Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_thanksgiving_day$$ | |
| CREATE FUNCTION is_thanksgiving_day | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Thanksgiving Day, 4th Thursday in November | |
| SET the_holiday = get_nth_weekday_of_month(4, 3, 11, the_year); | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_christmas_day() returns whether the date is Christmas Day | |
| -- | |
| DROP FUNCTION IF EXISTS is_christmas_day$$ | |
| CREATE FUNCTION is_christmas_day | |
| ( | |
| the_date DATE, | |
| bankers_time TINYINT(1) | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- the holiday | |
| DECLARE the_holiday DATE; | |
| -- year of the date | |
| DECLARE the_year INT(11); | |
| -- get year of the date | |
| SET the_year = YEAR(the_date); | |
| -- ##### | |
| -- Christmas Day, December 25 (bankers time; following Monday, if Sunday) | |
| SET the_holiday = CONCAT(the_year, "-12-25"); | |
| IF((bankers_time = 1) && (WEEKDAY(the_holiday) = 6)) THEN | |
| SET the_holiday = DATE_ADD(the_holiday, INTERVAL 1 DAY); | |
| END IF; | |
| IF(the_date = the_holiday) THEN | |
| RETURN(1); | |
| END IF; | |
| -- ##### | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_bank_holiday() returns whether date is a bank holiday | |
| -- | |
| DROP FUNCTION IF EXISTS is_bank_holiday$$ | |
| CREATE FUNCTION is_bank_holiday | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- recognized bank holidays | |
| -- New Year's Day, January 1 (bankers time; following Monday, if Sunday) | |
| -- Birthday of Martin Luther King, Jr., 3rd Monday in January | |
| -- Washington's Birthday, 3rd Monday in February | |
| -- Memorial Day, last Monday in May | |
| -- Independence Day, July 4 (bankers time; following Monday, if Sunday) | |
| -- Labor Day, 1st Monday in September | |
| -- Columbus Day, 2nd Monday in October | |
| -- Veterans Day, November 11 (bankers time; following Monday, if Sunday) | |
| -- Thanksgiving Day, 4th Thursday in November | |
| -- Christmas Day, December 25 (bankers time; following Monday, if Sunday) | |
| -- ##### | |
| -- get month of the date | |
| CASE MONTH(the_date) | |
| WHEN 1 THEN | |
| -- New Year's Day, January 1 (bankers time; following Monday, if Sunday) | |
| IF(is_new_years_day(the_date, 1) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| -- Birthday of Martin Luther King, Jr., 3rd Monday in January | |
| IF(is_mlkjr_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 2 THEN | |
| -- Washington's Birthday, 3rd Monday in February | |
| IF(is_washington_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 3 THEN | |
| -- no holiday in March | |
| RETURN(0); | |
| WHEN 4 THEN | |
| -- no holiday in April | |
| RETURN(0); | |
| WHEN 5 THEN | |
| -- Memorial Day, last Monday in May | |
| IF(is_memorial_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 6 THEN | |
| -- no holiday in June | |
| RETURN(0); | |
| WHEN 7 THEN | |
| -- Independence Day, July 4 (bankers time; following Monday, if Sunday) | |
| IF(is_independence_day(the_date, 1) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 8 THEN | |
| -- no holiday in August | |
| RETURN(0); | |
| WHEN 9 THEN | |
| -- Labor Day, 1st Monday in September | |
| IF(is_labor_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 10 THEN | |
| -- Columbus Day, 2nd Monday in October | |
| IF(is_columbus_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 11 THEN | |
| -- Veterans Day, November 11 (bankers time; following Monday, if Sunday) | |
| IF(is_veterans_day(the_date, 1) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| -- Thanksgiving Day, 4th Thursday in November | |
| IF(is_thanksgiving_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 12 THEN | |
| -- Christmas Day, December 25 (bankers time; following Monday, if Sunday) | |
| IF(is_christmas_day(the_date, 1) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| END CASE; | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_public_holiday() returns whether date is a public holiday | |
| -- | |
| DROP FUNCTION IF EXISTS is_public_holiday$$ | |
| CREATE FUNCTION is_public_holiday | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- public holidays | |
| -- New Year's Day, January 1 (not bankers time) | |
| -- Birthday of Martin Luther King, Jr., 3rd Monday in January | |
| -- Groundhog Day, February 2nd | |
| -- Washington's Birthday, 3rd Monday in February | |
| -- Valentine's Day, February 14th | |
| -- St. Patrick's Day, March 17th | |
| -- Easter, March 22nd - April 25th | |
| -- April Fools's Day, April 1st | |
| -- Easter, March 22nd - April 25th | |
| -- Mothers' Day, 2nd Sunday in May | |
| -- Memorial Day, last Monday in May | |
| -- Fathers' Day, 3rd Sunday in June | |
| -- Independence Day, July 4 (not bankers time) | |
| -- Parents' Day, 4th Sunday in July | |
| -- Labor Day, 1st Monday in September | |
| -- Grandparents' Day, Sunday after Labor Day | |
| -- Columbus Day, 2nd Monday in October | |
| -- Halloween, October 31st | |
| -- Veterans Day, November 11 (not bankers time) | |
| -- Thanksgiving Day, 4th Thursday in November | |
| -- Christmas Day, December 25 (not bankers time) | |
| -- ##### | |
| -- get month of the date | |
| CASE MONTH(the_date) | |
| WHEN 1 THEN | |
| -- New Year's Day, January 1 (not bankers time) | |
| IF(is_new_years_day(the_date, 0) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| -- Birthday of Martin Luther King, Jr., 3rd Monday in January | |
| IF(is_mlkjr_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 2 THEN | |
| -- Groundhog Day, February 2nd | |
| IF(is_groundhog_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| -- Valentine's Day, February 14th | |
| IF(is_valentines_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| -- Washington's Birthday, 3rd Monday in February | |
| IF(is_washington_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 3 THEN | |
| -- St. Patrick's Day, March 17th | |
| IF(is_st_patricks_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| -- Easter, March 22nd - April 25th | |
| IF(is_easter(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 4 THEN | |
| -- Easter, March 22nd - April 25th | |
| IF(is_easter(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| -- April Fools's Day, April 1st | |
| IF(is_april_fools_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 5 THEN | |
| -- Mothers' Day, 2nd Sunday in May | |
| IF(is_mothers_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| -- Memorial Day, last Monday in May | |
| IF(is_memorial_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 6 THEN | |
| -- Fathers' Day, 3rd Sunday in June | |
| IF(is_fathers_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 7 THEN | |
| -- Independence Day, July 4 (not bankers time) | |
| IF(is_independence_day(the_date, 0) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| -- Parents' Day, 4th Sunday in July | |
| IF(is_parents_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 8 THEN | |
| -- no holiday in August | |
| RETURN(0); | |
| WHEN 9 THEN | |
| -- Labor Day, 1st Monday in September | |
| IF(is_labor_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| -- Grandparents' Day, Sunday after Labor Day | |
| IF(is_grandparents_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 10 THEN | |
| -- Columbus Day, 2nd Monday in October | |
| IF(is_columbus_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| -- Halloween, October 31st | |
| IF(is_halloween(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 11 THEN | |
| -- Veterans Day, November 11 (not bankers time) | |
| IF(is_veterans_day(the_date, 0) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| -- Thanksgiving Day, 4th Thursday in November | |
| IF(is_thanksgiving_day(the_date) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| WHEN 12 THEN | |
| -- Christmas Day, December 25 (not bankers time) | |
| IF(is_christmas_day(the_date, 0) = 1) THEN | |
| RETURN(1); | |
| END IF; | |
| END CASE; | |
| RETURN(0); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- is_business_day() returns whether date is a weekend or bank holiday | |
| -- | |
| DROP FUNCTION IF EXISTS is_business_day$$ | |
| CREATE FUNCTION is_business_day | |
| ( | |
| the_date DATE | |
| ) | |
| RETURNS TINYINT(1) | |
| DETERMINISTIC | |
| BEGIN | |
| -- check if date date is a Saturday or Sunday | |
| IF(is_weekend(the_date) = 1) THEN | |
| RETURN(0); | |
| END IF; | |
| -- check if date is a bank holiday | |
| IF(is_bank_holiday(the_date) = 1) THEN | |
| RETURN(0); | |
| END IF; | |
| RETURN(1); | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- Populate Date Dimension | |
| -- | |
| -- | |
| -- id (day number) | |
| -- date | |
| -- day | |
| -- day_of_week | |
| -- day_name | |
| -- is_weekday | |
| -- is_weekend | |
| -- is_business_day | |
| -- is_bank_holiday | |
| -- is_public_holiday | |
| -- month | |
| -- month_name | |
| -- yearweek (e.g. "200621") | |
| -- yearweek_year (e.g. 2006) | |
| -- yearweek_week (e.g. 21) | |
| -- quarteryear (e.g. "Q22006") | |
| -- quarter (e.g. 2) | |
| -- year (e.g. 2006) | |
| -- | |
| DROP PROCEDURE IF EXISTS populate_dates$$ | |
| CREATE PROCEDURE populate_dates | |
| ( | |
| start_date DATE, | |
| end_date DATE | |
| ) | |
| BEGIN | |
| -- use date counter | |
| DECLARE the_date DATE; | |
| -- empty dates table | |
| TRUNCATE TABLE dates; | |
| -- set date counter, begin at start_date | |
| SET the_date = start_date; | |
| -- process all dates between start_date and end_date | |
| WHILE the_date <= end_date DO | |
| INSERT INTO dates VALUES | |
| ( | |
| TO_DAYS(the_date), | |
| the_date, | |
| DAYOFMONTH(the_date), | |
| WEEKDAY(the_date), | |
| DAYNAME(the_date), | |
| is_weekday(the_date), | |
| is_weekend(the_date), | |
| is_business_day(the_date), | |
| is_bank_holiday(the_date), | |
| is_public_holiday(the_date), | |
| MONTH(the_date), | |
| MONTHNAME(the_date), | |
| YEARWEEK(the_date), | |
| LEFT(YEARWEEK(the_date), 4), | |
| RIGHT(YEARWEEK(the_date), 2), | |
| CONCAT("Q", QUARTER(the_date), YEAR(the_date)), | |
| QUARTER(the_date), | |
| YEAR(the_date) | |
| ); | |
| -- increment date counter | |
| SET the_date = DATE_ADD(the_date, INTERVAL 1 DAY); | |
| END WHILE; | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- | |
| -- Populate Time Dimension | |
| -- | |
| -- | |
| -- id (seconds / id / INT) | |
| -- time_hms (hh:mm:ss / TIME) | |
| -- time_hm_id (seconds / id / INT) | |
| -- time_hm (hh:mm:00 / TIME) | |
| -- time_h_id (seconds / id / INT) | |
| -- time_h (hh:00:00 / TIME) | |
| -- hour (0...23 / INT) | |
| -- minute (0...59 / INT) | |
| -- second (0...59 / INT) | |
| -- hour_12 (1...12 / INT) | |
| -- is_am (0, 1 / TINYINT) | |
| -- is_pm (0, 1 / TINYINT) | |
| -- is_business (0, 1 / TINYINT) | |
| -- format_hms (hh:mm:ss / VARCHAR) | |
| -- format_hm (hh:mm:00 / VARCHAR) | |
| -- format_h (hh:00:00 / VARCHAR) | |
| -- format_hms_12 (hh:mm:ss mm / VARCHAR) | |
| -- format_hm_12 (hh:mm:00 mm / VARCHAR) | |
| -- format_h_12 (hh:00:00 mm / VARCHAR) | |
| -- | |
| DROP PROCEDURE IF EXISTS populate_times$$ | |
| CREATE PROCEDURE populate_times | |
| ( | |
| ) | |
| BEGIN | |
| -- use second counter | |
| DECLARE the_second INT(11); | |
| -- intermediate variables | |
| DECLARE tmp_seconds INT(11); | |
| DECLARE tmp_hour INT(11); | |
| DECLARE tmp_minute INT(11); | |
| DECLARE tmp_second INT(11); | |
| -- empty times table | |
| TRUNCATE TABLE times; | |
| -- set second counter, begin at midnight | |
| SET the_second = 0; | |
| WHILE the_second < 86400 DO | |
| -- set intermediate variable | |
| SET tmp_seconds = SEC_TO_TIME(the_second); | |
| SET tmp_hour = HOUR(tmp_seconds); | |
| SET tmp_minute = MINUTE(tmp_seconds); | |
| SET tmp_second = SECOND(tmp_seconds); | |
| -- populate time dimension | |
| INSERT INTO times VALUES | |
| ( | |
| the_second, | |
| tmp_seconds, | |
| TIME_TO_SEC(CONCAT(tmp_hour, ":", tmp_minute, ":00")), | |
| CONCAT(tmp_hour, ":", tmp_minute, ":00"), | |
| TIME_TO_SEC(CONCAT(tmp_hour, ":00:00")), | |
| CONCAT(tmp_hour, ":00:00"), | |
| tmp_hour, | |
| tmp_minute, | |
| tmp_second, | |
| TIME_FORMAT(tmp_seconds, "%l"), | |
| TIME_FORMAT(tmp_seconds, "%p") = "AM", | |
| TIME_FORMAT(tmp_seconds, "%p") = "PM", | |
| the_second BETWEEN 32400 AND 61200, | |
| TIME_FORMAT(tmp_seconds, "%H:%i:%s"), | |
| TIME_FORMAT(tmp_seconds, "%H:%i:00"), | |
| TIME_FORMAT(tmp_seconds, "%H:00:00"), | |
| TIME_FORMAT(tmp_seconds, "%I:%i:%s %p"), | |
| TIME_FORMAT(tmp_seconds, "%I:%i:00 %p"), | |
| TIME_FORMAT(tmp_seconds, "%I:00:00 %p") | |
| ); | |
| -- increment second counter | |
| SET the_second = the_second + 1; | |
| END WHILE; | |
| END$$ | |
| -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | |
| -- CALL populate_dates('2012-01-01', '2012-06-30')$$ | |
| -- CALL populate_times()$$ | |
| -- =================================================================== |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Various MySQL stored procedures for handling dates, as well as populating date and time dimensions.