mysql时间字段修改成为某时间段内的随机时间

先将20010101 11:00  20131010 12:12转换为时间戳,如一个是time1  另一个是time2

//查出a表中的所有字段(只取出B字段)

$time1 = strtotime('20010101 11:00')

$time2= strtotime('20131010 12:12')

$sql = "select b from a"

//执行sql语句得到 $barr

foreach($barr as $key=>$val){ //a表中的序号假设为id

    $time = rand($time1,$time2)

    $sql = "update a set b='".$time."' where id = $val['id']"

    //执行sql语句  

}

希望能对你有所帮助

CREATE TABLE test_random_time ( id int, newstime datetime)INSERT INTO test_random_timeSELECT 1, '2012-11-13 01:00:00' UNION ALLSELECT 2, '2012-11-13 02:00:00' UNION ALLSELECT 3, '2012-11-13 03:00:00' UNION ALLSELECT 4, '2012-11-13 04:00:00' UNION ALLSELECT 5, '2012-11-13 05:00:00' -- 20点至23点。-- 区间=3小时=180分钟=10800秒-- 下面更新时间 = '2012-11-13 20:00:00' 之后的 随机秒数。（区间在 1- 10800 之间）UPDATE test_random_timeSET newstime = DATE_ADD('2012-11-13 20:00:00', INTERVAL FLOOR(1 + (RAND() * 10800)) SECOND )WHERE DATE(newstime) = '2012-11-13' -- 数据核对.mysql>SELECT * FROM test_random_time+------+---------------------+| id | newstime|+------+---------------------+|1 | 2012-11-13 22:25:14 ||2 | 2012-11-13 22:41:16 ||3 | 2012-11-13 20:10:35 ||4 | 2012-11-13 21:49:08 ||5 | 2012-11-13 22:33:55 |+------+---------------------+5 rows in set (0.00 sec)

--建立过程

create procedure gettime

@begintime varchar(5),@EndTime varchar(5),@randtime varchar(5) output

as

set @randtime=(SELECT substring(convert(varchar,convert(datetime,100/RAND(),120),120),12,5) )

while @randtime<=@begintime or @randtime>=@endtime

set @randtime=(SELECT substring(convert(varchar,convert(datetime,100/RAND(),120),120),12,5) )

go

--执行示例

declare @outtime varchar(5)

exec hygettime '05:00','20:00',@outtime output

select @outtime

---