PostgreSQL 获取两个日期时间/时间戳之间的随机日期时间/时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22964272/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL Get a random datetime/timestamp between two datetime/timestamp
提问by Matthiew
The title is pretty much explicit, my question is if i get two dates with hour:
标题非常明确，我的问题是我是否有两个带小时的日期：
- 01-10-2014 10:00:00
- 01-20-2014 20:00:00
- 01-10-2014 10:00:00
- 01-20-2014 20:00:00
Is it possible to pick a random datetime between these two datetime ?
是否可以在这两个日期时间之间选择一个随机日期时间？
I tried with the random() function but i don't really get how to use it with datetime
我尝试使用 random() 函数，但我真的不知道如何将它与 datetime 一起使用
Thanks
谢谢
Matthiew
马修
回答by pozs
You can do almost everything with the date/time operators:
select timestamp '2014-01-10 20:00:00' +
random() * (timestamp '2014-01-20 20:00:00' -
timestamp '2014-01-10 10:00:00')
回答by Dustin
I adapted @pozs answer, since I didn't have timestamps to go off of.
我改编了@pozs 的答案，因为我没有要关闭的时间戳。
90 daysis the time window you want and the 30 daysis how far out to push the time window. This is helpful when running it via a job instead of at a set time.
90 days是您想要的时间窗口，是将时间窗口30 days推多远。当通过作业而不是在设定的时间运行它时，这很有用。
select NOW() + (random() * (NOW()+'90 days' - NOW())) + '30 days';
回答by Denis de Bernardy
You could build the timestamp from a random integer (unix stamp), e.g.:
您可以从随机整数（unix 戳）构建时间戳，例如：
select timestamp 'epoch' + (
extract('epoch' from timestamp '2014-10-01 10:00:00')
+ random() * (
extract('epoch' from timestamp '2014-20-01 20:00:00')
- extract('epoch' from timestamp '2014-10-01 10:00:00')
)) * interval '1 second'
Wrap it in an SQL function if you use it often, as it's not very readable even after attempting to format it somewhat.
如果您经常使用它，请将其包装在 SQL 函数中，因为即使尝试对其进行某种格式化后，它的可读性也不是很好。
Another way to do it would be to use generate_series()from the start date to the end date, ordered by random(), but that would make things very slow with larger date intervals so you'll be better off with the above approach.
另一种方法是使用generate_series()从开始日期到结束日期，按 random() 排序，但这会使较大的日期间隔变得非常慢，因此使用上述方法会更好。
