--
获取年月的存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS `radius`.`mygetyearmonth`$$
CREATE DEFINER = `radius`@` % ` PROCEDURE `mygetyearmonth`( IN datebegin int , IN dateend int )
BEGIN
DECLARE MidDate datetime ;
Set MidDate = DATE_FORMAT(concat( left (datebegin, 4 ), ' - ' , right (datebegin, 2 ), ' -01 ' ), ' %Y-%m-01 00:00:00 ' );
CREATE TEMPORARY TABLE MyYearMonth(
yearmonth int
);
WHILE EXTRACT(YEAR_MONTH FROM MidDate) <= dateend DO
insert into MyYearMonth(yearmonth) values (EXTRACT(YEAR_MONTH FROM MidDate));
Set MidDate = DATE_ADD(MidDate, INTERVAL 1 month );
END WHILE ;
select * from MyYearMonth;
DROP TEMPORARY TABLE MyYearMonth;
END $$
DELIMITER ;
-- 过程结束
调用执行存储过程
call mygetyearmonth( ' 200601 ' , ' 200702 ' )
返回结果集
" 200601 "
" 200602 "
" 200603 "
" 200604 "
" 200605 "
" 200606 "
" 200607 "
" 200608 "
" 200609 "
" 200610 "
" 200611 "
" 200612 "
" 200701 "
" 200702 "
-- 获取某段时间包含多少周的存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS `radius`.`mygetyearweeks`$$
CREATE DEFINER = `radius`@` % ` PROCEDURE `mygetyearweeks`( IN datebegin datetime , IN dateend datetime )
BEGIN
DECLARE MidDate datetime ;
DECLARE num int ;
Set MidDate = DATE_FORMAT(date_add(datebegin,INTERVAL - (DAYOFWEEK(datebegin) - 1 ) + 1 DAY ), ' %Y-%m-%d 00:00:00 ' );
SET num = 1 ;
CREATE TEMPORARY TABLE MyYearWeek(
num int ,
begintime datetime ,
endtime datetime
);
WHILE MidDate < dateend DO
insert into MyYearWeek(num,begintime,endtime) values
(num,
DATE_FORMAT(MidDate, ' %Y-%m-%d 00:00:00 ' ),
DATE_FORMAT(DATE_ADD(MidDate, INTERVAL 6 DAY ), ' %Y-%m-%d 23:59:59 ' )
);
Set MidDate = DATE_ADD(MidDate, INTERVAL 7 DAY );
Set num = num + 1 ;
END WHILE ;
select * from MyYearWeek;
DROP TEMPORARY table MyYearWeek ;
END $$
DELIMITER ;
-- 过程结束
调用执行存储过程
call mygetyearweeks( ' 2008-01-03 ' , ' 2008-04-09 ' )
返回结果集
num,begintime,endtime
" 1 "," 2007 - 12 - 31 00 : 00 : 00 "," 2008 - 01 - 06 23 : 59 : 59 "
" 2 "," 2008 - 01 - 07 00 : 00 : 00 "," 2008 - 01 - 13 23 : 59 : 59 "
" 3 "," 2008 - 01 - 14 00 : 00 : 00 "," 2008 - 01 - 20 23 : 59 : 59 "
" 4 "," 2008 - 01 - 21 00 : 00 : 00 "," 2008 - 01 - 27 23 : 59 : 59 "
" 5 "," 2008 - 01 - 28 00 : 00 : 00 "," 2008 - 02 - 03 23 : 59 : 59 "
" 6 "," 2008 - 02 - 04 00 : 00 : 00 "," 2008 - 02 - 10 23 : 59 : 59 "
" 7 "," 2008 - 02 - 11 00 : 00 : 00 "," 2008 - 02 - 17 23 : 59 : 59 "
" 8 "," 2008 - 02 - 18 00 : 00 : 00 "," 2008 - 02 - 24 23 : 59 : 59 "
" 9 "," 2008 - 02 - 25 00 : 00 : 00 "," 2008 - 03 - 02 23 : 59 : 59 "
" 10 "," 2008 - 03 - 03 00 : 00 : 00 "," 2008 - 03 - 09 23 : 59 : 59 "
" 11 "," 2008 - 03 - 10 00 : 00 : 00 "," 2008 - 03 - 16 23 : 59 : 59 "
" 12 "," 2008 - 03 - 17 00 : 00 : 00 "," 2008 - 03 - 23 23 : 59 : 59 "
" 13 "," 2008 - 03 - 24 00 : 00 : 00 "," 2008 - 03 - 30 23 : 59 : 59 "
" 14 "," 2008 - 03 - 31 00 : 00 : 00 "," 2008 - 04 - 06 23 : 59 : 59 "
" 15 "," 2008 - 04 - 07 00 : 00 : 00 "," 2008 - 04 - 13 23 : 59 : 59 "
DELIMITER $$
DROP PROCEDURE IF EXISTS `radius`.`mygetyearmonth`$$
CREATE DEFINER = `radius`@` % ` PROCEDURE `mygetyearmonth`( IN datebegin int , IN dateend int )
BEGIN
DECLARE MidDate datetime ;
Set MidDate = DATE_FORMAT(concat( left (datebegin, 4 ), ' - ' , right (datebegin, 2 ), ' -01 ' ), ' %Y-%m-01 00:00:00 ' );
CREATE TEMPORARY TABLE MyYearMonth(
yearmonth int
);
WHILE EXTRACT(YEAR_MONTH FROM MidDate) <= dateend DO
insert into MyYearMonth(yearmonth) values (EXTRACT(YEAR_MONTH FROM MidDate));
Set MidDate = DATE_ADD(MidDate, INTERVAL 1 month );
END WHILE ;
select * from MyYearMonth;
DROP TEMPORARY TABLE MyYearMonth;
END $$
DELIMITER ;
-- 过程结束
调用执行存储过程
call mygetyearmonth( ' 200601 ' , ' 200702 ' )
返回结果集
" 200601 "
" 200602 "
" 200603 "
" 200604 "
" 200605 "
" 200606 "
" 200607 "
" 200608 "
" 200609 "
" 200610 "
" 200611 "
" 200612 "
" 200701 "
" 200702 "
-- 获取某段时间包含多少周的存储过程
DELIMITER $$
DROP PROCEDURE IF EXISTS `radius`.`mygetyearweeks`$$
CREATE DEFINER = `radius`@` % ` PROCEDURE `mygetyearweeks`( IN datebegin datetime , IN dateend datetime )
BEGIN
DECLARE MidDate datetime ;
DECLARE num int ;
Set MidDate = DATE_FORMAT(date_add(datebegin,INTERVAL - (DAYOFWEEK(datebegin) - 1 ) + 1 DAY ), ' %Y-%m-%d 00:00:00 ' );
SET num = 1 ;
CREATE TEMPORARY TABLE MyYearWeek(
num int ,
begintime datetime ,
endtime datetime
);
WHILE MidDate < dateend DO
insert into MyYearWeek(num,begintime,endtime) values
(num,
DATE_FORMAT(MidDate, ' %Y-%m-%d 00:00:00 ' ),
DATE_FORMAT(DATE_ADD(MidDate, INTERVAL 6 DAY ), ' %Y-%m-%d 23:59:59 ' )
);
Set MidDate = DATE_ADD(MidDate, INTERVAL 7 DAY );
Set num = num + 1 ;
END WHILE ;
select * from MyYearWeek;
DROP TEMPORARY table MyYearWeek ;
END $$
DELIMITER ;
-- 过程结束
调用执行存储过程
call mygetyearweeks( ' 2008-01-03 ' , ' 2008-04-09 ' )
返回结果集
num,begintime,endtime
" 1 "," 2007 - 12 - 31 00 : 00 : 00 "," 2008 - 01 - 06 23 : 59 : 59 "
" 2 "," 2008 - 01 - 07 00 : 00 : 00 "," 2008 - 01 - 13 23 : 59 : 59 "
" 3 "," 2008 - 01 - 14 00 : 00 : 00 "," 2008 - 01 - 20 23 : 59 : 59 "
" 4 "," 2008 - 01 - 21 00 : 00 : 00 "," 2008 - 01 - 27 23 : 59 : 59 "
" 5 "," 2008 - 01 - 28 00 : 00 : 00 "," 2008 - 02 - 03 23 : 59 : 59 "
" 6 "," 2008 - 02 - 04 00 : 00 : 00 "," 2008 - 02 - 10 23 : 59 : 59 "
" 7 "," 2008 - 02 - 11 00 : 00 : 00 "," 2008 - 02 - 17 23 : 59 : 59 "
" 8 "," 2008 - 02 - 18 00 : 00 : 00 "," 2008 - 02 - 24 23 : 59 : 59 "
" 9 "," 2008 - 02 - 25 00 : 00 : 00 "," 2008 - 03 - 02 23 : 59 : 59 "
" 10 "," 2008 - 03 - 03 00 : 00 : 00 "," 2008 - 03 - 09 23 : 59 : 59 "
" 11 "," 2008 - 03 - 10 00 : 00 : 00 "," 2008 - 03 - 16 23 : 59 : 59 "
" 12 "," 2008 - 03 - 17 00 : 00 : 00 "," 2008 - 03 - 23 23 : 59 : 59 "
" 13 "," 2008 - 03 - 24 00 : 00 : 00 "," 2008 - 03 - 30 23 : 59 : 59 "
" 14 "," 2008 - 03 - 31 00 : 00 : 00 "," 2008 - 04 - 06 23 : 59 : 59 "
" 15 "," 2008 - 04 - 07 00 : 00 : 00 "," 2008 - 04 - 13 23 : 59 : 59 "