mysql - 选择随机日期范围

Question

我正在尝试从具有随机日期范围的表格中进行选择。有点像

SELECT * FROM my_table WHERE timestamp 
BETWEEN <random_date> AND <the_same_random_date> - INTERVAL 1 DAY

这样我应该选择一个随机日期的 1 天间隔。有什么建议吗？

更新:因此随机查询可能与以下查询具有相同的效果:

SELECT * FROM my_table WHERE timestamp 
BETWEEN '2011-08-05 00:00' AND '2011-08-05 00:00' - INTERVAL 1 DAY

SELECT * FROM my_table WHERE timestamp 
BETWEEN '2011-08-12 14:30' AND '2011-08-12 14:30' - INTERVAL 1 DAY

SELECT * FROM my_table WHERE timestamp 
BETWEEN '2011-05-11 06:15' AND '2011-05-11 06:15' - INTERVAL 1 DAY

SELECT * FROM my_table WHERE timestamp 
BETWEEN '2011-03-03 21:11' AND '2011-03-03 21:11' - INTERVAL 1 DAY

Answer 1

SELECT * 
FROM 
    my_table
    CROSS JOIN (SELECT FROM_UNIXTIME(RAND() * 2147483647) random_date) rd
WHERE 
    timestamp BETWEEN (rd.random_date - INTERVAL 1 DAY) AND rd.random_date