函数:
DELIMITER $$
CREATE
FUNCTION `RandomDateTime`(sd DATETIME,ed DATETIME)
RETURNS DATETIME
BEGIN
RETURN DATE_ADD(sd,INTERVAL FLOOR(1+RAND()*((ABS(UNIX_TIMESTAMP(ed)-UNIX_TIMESTAMP(sd)))-1)) SECOND);
END$$
DELIMITER ;
好看一点的
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);
测试:
SELECT 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;