MySQL在给定的日期时间范围内插入随机的日期时间 [英] Mysql insert random datetime in a given datetime range

问题描述

使用SQL，我可以在提供范围的列中插入随机的datetime值吗?

With SQL , Can I insert random datetime values in a column giving a range?

例如，给定范围为2010-04-30 14:53:27到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))
    )
)

它使用日期2010-04-30 14:53:27作为基准，将其转换为Unix时间戳，并在0到+2年的基础日期中添加随机的秒数，并将其转换回DATETIME.

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.

它应该很接近，但在更长的时间内会leap年，其他调整将使它失效.

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

这篇关于MySQL在给定的日期时间范围内插入随机的日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！