With SQL , Can I insert random datetime values in a column giving a range?
For example, 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
INSERT INTO `sometable` VALUES (RND (DATETIME()))
解决方案
Here is an example that should help:
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.