MySQL 生成随机时间函数（转）

MySQL 生成随机时间函数（转）

 

DROP FUNCTION IF EXISTS func_RandomDateTime; 

#USAGE:
#SELECT func_RandomDateTime(DATE_FORMAT('2010-12-1 10:10:10','%Y-%m-%d %H:%i:%s'),DATE_FORMAT('2010-12-1 11:10:13','%Y-%m-%d %H:%i:%s')) AS t;
#

CREATE
 FUNCTION func_RandomDateTime(
    sd DATETIME,
    ed DATETIME)
 RETURNS DATETIME

BEGIN

 DECLARE sub INT DEFAULT 0;
 DECLARE ret DATETIME;
 SET sub = ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd));
 SET ret = DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*(sub-1)) SECOND);

 RETURN DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*((ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd)))-1)) SECOND);

END;