39 lines
927 B
MySQL
39 lines
927 B
MySQL
|
DELIMITER $$
|
||
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `util`.`time_generate`(vStarted DATE, vEnded DATE)
|
||
|
|
BEGIN
|
||
|
|
/**
|
||
|
|
* Generate a temporary table between the days passed as parameters
|
||
|
|
*
|
||
|
|
* @param vStarted Start date
|
||
|
|
* @param vEnded End date
|
||
|
|
* @table tmp.time (dated, dayed, weeked, monthed, yeared)
|
||
|
|
*/
|
||
|
|
DECLARE vCurrentDate DATE;
|
||
|
|
|
||
|
|
CREATE OR REPLACE TEMPORARY TABLE tmp.time(
|
||
|
|
dated DATE PRIMARY KEY,
|
||
|
|
`day` INT,
|
||
|
|
`week` INT,
|
||
|
|
`month` INT,
|
||
|
|
`year` INT
|
||
|
|
) ENGINE = MEMORY;
|
||
|
|
|
||
|
|
IF(vStarted > vEnded) THEN
|
||
|
|
CALL throw('The started cannot be later than the ended');
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
SET vCurrentDate = vStarted;
|
||
|
|
|
||
|
|
WHILE vCurrentDate <= vEnded DO
|
||
|
|
INSERT INTO tmp.time
|
||
|
|
SET dated = vCurrentDate,
|
||
|
|
`day` = DAY(vCurrentDate),
|
||
|
|
`week` = WEEK(vCurrentDate),
|
||
|
|
`month` = MONTH(vCurrentDate),
|
||
|
|
`year` = YEAR(vCurrentDate);
|
||
|
|
|
||
|
|
SET vCurrentDate = vCurrentDate + INTERVAL 1 DAY;
|
||
|
|
END WHILE;
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|