网络营销电子商务研究中心 - Mysql insert random datetime in a given datetime range

网络营销电子商务研究中心 (https://www.0058.net/index.php)

- MySQL (https://www.0058.net/forumdisplay.php?f=76)

- - Mysql insert random datetime in a given datetime range (https://www.0058.net/showthread.php?t=5093)

Imperial

2015-04-19 06:14 AM

Mysql insert random datetime in a given datetime range

With SQL , Can I insert random datetime values in a column giving a range? eg. given a range of 2010-04-30 14:53:27 to 2012-04-30 14:53:27

I'm getting confused with the range part. as i will have just done this

Code:

INSERT INTO `sometable` VALUES (RND (DATETIME()))

Imlay City

2015-04-19 06:16 AM

Here is an example that should help:

Code:

INSERT INTO `sometable` VALUES(

    FROM_UNIXTIME(

        UNIX_TIMESTAMP('2010-04-30 14:53:27') + FLOOR(0 + (RAND() * 63072000))

    )

)

It uses the date 2010-04-30 14:53:27 as the base, converts that to a Unix timestamp, and adds a random number of seconds from 0 to +2 years to the base date and converts it back to a DATETIME.

It should be pretty close but over longer time periods leap years and other adjustments will throw it off.

All times are GMT +8. The time now is 12:21 AM.